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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube