Let's say I have a pair of four data points:
A B C D
90 80 70 60
90 90 90
In column E, I hope to have a function, something like:
=((A1*4)+(B1*3)+(C1*2)+(D1))/10
How can I adjust this formula so that, if there is ever an empty cell, the formula automatically removes that cell from the formula? In doing so, this should change the denominator of the final formula; if C is an empty cell, I want to divide the entire formula by 8, not by 10 (since C is counted twice in the ultimate 10-count of the formula).
This is what SUMPRODUCT may used for.
=SUMPRODUCT(A1:D1,{4,3,2,1})
will calculate A1*4+B1*3+C1*2+D1*1 and if either A1, B1, C1 or D1 is blank this leads to 0 in 0*n then.
And in
=SUMPRODUCT(NOT(ISBLANK(A1:D1)),{4,3,2,1})
the NOT(ISBLANK(A1:D1) leads to {1,1,1,1} if A1, B1, C1 and D1 are not blank or it leads to {1,1,0,1} if C1 is blank. So the formula leads to 10 (4+3+2+1) if all are not blank and to 8 (4+3+0+1) if C1 is blank.
Putting it all together:
=SUMPRODUCT(A1:D1,{4,3,2,1})/SUMPRODUCT(NOT(ISBLANK(A1:D1)),{4,3,2,1})