The Best-Selling Smartphone
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 Excel example 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 (as Example) 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 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 items 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 VLOOKUP function could get some results that can later be used in the graph on the chart.
The formula in B26 cell: =SUM(J2:J6) => copied down
To copy and grouped the unique company names in column 'B' was used combinations of INDEX, MATCH and COUNTIF functions. As you can see COUNTIF function is nested within the MATCH function together are inserted within the Excel INDEX function and form a joint ARRAY formula.
This is the formula that copies the unique name (brand name) in 'B' column range of cells B2:B23
Array formula in cell C26:
Array formula in cell C27:
=> 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 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:
=> (If this formula you copy from this tutorials, the formula must be in one line)
This formula above based on the largest sum in 'J' column, returns the name of the type of mobile phones in 'C' column.
The most sold mobile phones - the company name
To calculate the number of the most sold mobile phones formula was used with the Excel MAX, functions which is nested in a VLOOKUP function. This VLOOKUP function 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:
At least sold mobile phones - the company name
To calculate the number of the most sold mobile phones formula was used with the Excel MIN functions, which is nested in a VLOOKUP function. This VLOOKUP function 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:
Total pieces of best-selling mobile phones
To find out how the best-selling mobile phones in numbers, we will use Excel MAX, functions for 'J' column.
The average price of a Smartphone 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'.
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 MAX, functions or 'D' column.
Name the most expensive Smartphones 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 'D' column to return name ie. The type of cell phone is a long formula that uses multiple Excel functions (INDEX, IF, SUMPRODUCT, MAX, NA ROW).
=> (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)
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 Excel VLOOKUP Formula copied to the right. This formula is not necessary to copy.
This formula works by finding the maximum value in 'D' column in which the price and based on the detected value returns the data from the column to the left in the same row. Used VLOOKUP, MAX and CHOOSE functions. 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' range.
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'
ATTENTION! Prices shown and the number of Smartphones sold are not real and are taken as an example in this tutorial.