HELP Subtracting averages when some cells have no values

[ztaylor0]

Below I have provided an example of what I am looking for. Basically I need to subtract two averages. Average of Rows 2-100 minus the average of row 1. Row 1 will be subtracted from the average of every other row. Here is the tricky part if for example row 3 does not have a value in cell B3 or C3 then I need the average of row 1 to exclude B1 and C1 from its calculation.

A B C
1 - 69.1 72 71

2 - 85 83 84 this would be the average of 85,83,84(84) minus the average of 69.1,72,71(70.7) result should be 84-70.7=13.3
3 - 86 this would be the average of 86(86) minus the average of 69.1(69.1) result should be 86-69.1=16.9
4 - 87 90 this would be the average of 87,90(88.5) minus the average of 69.1,72(70.55) result should be 88.5-70.55=17.95

Please help. All suggestions are appreciated.

[dredwolf]

Using your sample above:
=AVERAGE(A2:C2)-SUMPRODUCT(SUM((A2:C2>0)*$A$1:$C$1)/SUM(--(A2:C2>0)))
drag down

Hope this helps

[ztaylor0]

Awesome, Thanks for the quick response. Worked perfectly!

[icestationzbra]

slightly shorter, with fewer functions, in cell D3 and drag-filled down:

Please Login or Register  to view this content.