Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

The best-selling mobile phone

What is the most sold cell phone in the last five months

In this tutorial I want to show an example of how to calculate the various elements related to the sale of mobile phones in the last year.

In the tutorial you can see the formula by which you can calculate the following:
  • average sales of mobile phones for the past five months
  • best selling cell phone - type - model
  • most sold cell phones
  • a minimum sold cell phones
  • total pieces of best-selling mobile phones
  • average price of mobile phones
  • most expensive cell phone prices
  • the name of the most expensive mobile phones to basis of prices
  • the highest earnings for mobile phone based on the column SUM AMOUNT:
In this tutorial I have used the names of some types of cell phones (of course there are more) but this is a short tutorial that will show you how to use Excel to various calculations, statistics and calculations to get the desired statistics for the sale of mobile phones. This is just a small example of a data table from which it can be visually or formula to find out a specific statistic. However, imagine that you have such a table with hundreds of rows and dozens of columns. Then we can say that this formula at the end of the tutorial very much welcome. (Although this is a tutorial for beginners).
  • Alcatel 20.05
  • Alcatel OT-4030 S'pop
  • Alcatel Tablet T10
  • Alcatel OT-918 MIX
  • Alcatel OT-6030 Idol
  • HTC Desire X
  • HTC Desire 300
  • HTC Desire 500
  • HTC One
  • HTC Desire 200
  • Nokia Lumia 520
  • Nokia Asha 205
  • Nokia Asha 201
  • Nokia Lumia 920
  • Nokia Lumia 620
  • Samsung Galaxy S4
  • Samsung Galaxy S5
  • Sony Xperia E
  • Sony Xperia Z
  • Sony Xperia M
  • Sony Xperia Z1
  • Sony Xperia SP
The table shown below and the picture you see how organized range of data for statistical calculations.

The best-selling cell phone

The following formula you can use to calculate the statistic for the following elements of statistics sales of mobile phones.
Attention! If your computer have different regional settings from me, in the formulas instead of a semicolon (;) you need to use a comma (,)

How to extract unique for businesses

In a small table you see the data in two columns (B26:C30). In a range of cells "B26:B30" is a formula that adds all the quantities sold cell phones for five months. In this formula was used SUM function. This small table serves as a subsidiary to using the functions VLOOKUP could get some results that can later be used in the graph on the chart.

The formula in cell B26:  =SUM(J2:J6) => copied down

To copy and grouped the unique company names in column 'B' was used combinations of functions INDEX, MATCH and COUNTIF. As you can see COUNTIF function is nested within the Match function together are inserted within the index function and form a joint ARRAY formula.

This is the formula that copies the unique name (brand name) in column 'B' range of cells B2:B23

Array formula in cell C26:
=INDEX($B$2:$B$23;MATCH(0;COUNTIF($B$2:$B$23;"<"&$B$2:$B$23);0))

Array formula in cell C27:
=IF(COUNTIF($B$2:$B$23;">"&C26);INDEX($B$2:$B$23;MATCH(COUNTIF($B$2:$B$23;"<="&C26);COUNTIF($B$2:$B$23;"<"&$B$2:$B$23);0));"")
=> copied down (if you copy this formula from this tutorial, the formula must be in one line)

The average cell phones for a certain period of last months

The average number of cell phones sold for the same period was used the AVERAGE function that is referenced in the range of cells E2:J23

The formula for this statistical data is: =AVERAGE(E2:J23)

The best-selling type of cell phone for the time period

To know which is the best selling cell phone can use multiple Excel nested functions. For more details on the used functions, see their syntax to link Excel functions.

The formula for calculating the best-selling mobile phone is:
=INDEX(C2:C23;IF(SUMPRODUCT((C2:K23=MAX(J2:J23))*1)<>1;NA();SUMPRODUCT(ROW(C2:K23)*($C$2:$K$23=MAX(J2:J23)))-ROW(C2:K23)+1))
=> (If this formula you copy from this tutorials, the formula must be in one line)

This formula above based on the largest sum in column 'J' returns the name of the type of mobile phones in column 'C'

The most sold mobile phones - the company name

To calculate the number of the most sold mobile phones formula was used with the functions MAX, which is nested in a VLOOKUP function. This function VLOOKUP we used on a smaller table as I previously wrote.

A formula that gives back as a result of the Company in relation to the largest number of mobile phones sold:
=VLOOKUP(MAX(B26:B30);B26:C30;2;FALSE)

At least sold mobile phones - the company name

To calculate the number of the most sold mobile phones formula was used with the functions MIN, which is nested in a VLOOKUP function. This function VLOOKUP we used on a smaller table as I previously wrote.

A formula that gives back as a result of the Company in relation to the minimum number of mobile phones sold:
=VLOOKUP(MIN(B26:B30);B26:C30;2;FALSE)

Total pieces of best-selling mobile phones

To find out how the best-selling mobile phones in numbers, we will use Excel MAX function for column 'J'.

=MAX(J2:J23)

The average price of a cell phone during the last five months

To find out what is the average price of all mobile phones that are in the database table using Excel AVERAGE function for column 'D'.

=AVERAGE(D2:D23)

The most expensive cell phone prices in the last five months

To find out which type of cell phone or model is the most expensive we will again use the Excel function MAX or column 'D'.

=MAX(D2:D23)

Name the most expensive mobile phones to basis of prices

As I said earlier that this is just an example of less database tables. That is a table with hundreds of rows and dozens of columns and maybe some other products not easy reach statistical data, no formulas. In this case we are looking for the name of the most expensive mobile phone from a database table based on its selling price. I'll tell you, if I look at the table base data that this type of mobile phone "Nokia Asha 201". I repeat, it is now easy on the basis of this small database tables to visually determine whether they on a large database table.

To return the items, ie. The most expensive type of mobile phones can be used a minimum three types of formulas.

The first formula that will based on the price in column 'D' to return name ie. The type of cell phone is a long formula that uses multiple Excel functions (INDEX, IF, SUMPRODUCT, MAX, NA ROW).

=INDEX(C2:C23;IF(SUMPRODUCT((C2:D23=MAX(D2:D23))*1)<>1;NA();SUMPRODUCT(ROW(C2:D23)*(C2:D23=MAX(D2:D23)))-ROW(C2:D23)+1))
=> (If you copy this formula with this tutorial, the formula must be in one line)

Another formula that returns the same result as the previous one is much simpler (I put this just to show you how one result can be obtained with several different formulas)

=INDEX(C2:C23;MATCH(MAX(D2:D23);D2:D23;0))

The third formula that returns the same result as the previous one is known Excel VLOOKUP function but in this case it is called the formula "VLOOKUP Left" or "VLOOKUP to the left" because it is the base column to the right as opposed to a database table when the column base with left hand while VLOOKUP formula copied to the right. This formula is not necessary to copy.

This formula works by finding the maximum value in column D in which the price and based on the detected value returns the data from the column to the left in the same row. Used functions VLOOKUP, MAX and CHOOSE function. In this case Choose Excel function uses two numbers: 1 and 2. Number 1 is linked to a range of cells 'D2:D23' and the number two is related to the 'C2:C23'.
Choose Excel function used as an argument 'table_array' within the VLOOKUP function. Try to see the Evaluate Formula and see how running calculation of this formula.

=VLOOKUP(MAX(D2:D23);CHOOSE({1\2};D2:D23;C2:C23;);2;FALSE)

The maximum earnings for mobile phone based on column Sum Amount:

To calculate the type of cell phone is the largest earnings can also use the 'Left VLOOKUP'

=VLOOKUP(MAX(K2:K23);CHOOSE({1\2};K2:K23;C2:C23;);2;FALSE)

Chart selling mobile phones in five months


ATTENTION! Prices are shown and the number of mobile phones sold are not real and are taken as an example in this tutorial.