|  

» Disregarding blank cells when performing calculations on a range.

Problem:

Listed in Range A1:C13 are the totals of sales made by each salesman per date.
A blank cell in column E, indicates that no sales were made by that salesman on that date.
How could we calculate the averaged total of sales per each date and per each salesman, disregarding blank cells?

Solution:

Using the AVERAGE and IF functions in an Array Formula, such as this:
{=AVERAGE(IF($A$5:$A$13=A17,IF($B$5:$B$13=B17,IF($C$5:$C$13<>"""",$C$5:$C$13))))}

(To create Array formula: select the cell after typing the formula,press F2 and then press Ctrl+Shift+Enter)

Example:

Range1

Date___________ Salesman ID_____Total Sales
10/04/2005_____ 1_______________$10,000
10/04/2005_____ 2_______________$8,000
10/04/2005_____ 3_______________
11/04/2005_____ 1_______________
11/04/2005_____ 2_______________$5,000
11/04/2005_____ 3_______________$12,000
10/04/2005_____ 1_______________$9,000
12/04/2005_____ 2_______________
12/04/2005_____ 3_______________$12,500

Range2

Date___________Salesman ID______Averaged Sales
10/04/2005_____1________________$9,500
11/04/2005_____2________________$5,000
12/04/2005_____3________________$12,500

Screenshot // Disregarding blank cells when performing calculations on a range.
Disregarding blank cells when performing calculations on a range.


Rate This Tip
12 34 5
Rating: 2.58     Views: 20045
WesZalewski
Unclear.
What is in cells A17, B17 and what column E has to do with anything in this example? Blank cells seem to be in column C.

If range two shows results then the avarage for Salesman ID 3 should be 12,250.
Please, clarify.
Reply: WesZalewski
Alan
Hi WesZalewski,

[QUOTE=WesZalewski]Unclear.
What is in cells A17, B17 and what column E has to do with anything in this example? Blank cells seem to be in column C.

If range two shows results then the avarage for Salesman ID 3 should be 12,250.
Please, clarify.[/QUOTE]It appears to me that part of the formula is missing in the example.

Have you reported this using the 'report post' function?

I don't want to double report it, so I have not done so, but you can do that yourself, by clicking on the botton.

HTH,

Alan.
Click here to post comment
For Registered Users
Name
Comment Title
Comments