» 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.