Forum Discussion
Average
I am trying to establish the average value of a series of numbers in a string of individual cells in Excel. So, for example I have spreadsheet with whole number values in numerous separate cells that...
Hi, SergeiBaklan. You are quite right, I have tried your suggestion by using your formula exactly, but it registers an answer of "0"? when, in my test, I have populated a value of 28, 28, and 2 in the 1st, 5th and 9th column of my model, which should give me an answer of 19.333 (i.e. 58/3).
Any ideas, please?
topcat999 , yes, we shall to exclude blanks. If exclude both zeroes and blanks when
=SUMPRODUCT(J6:LR6*(MOD(COLUMN(J6:LR6),4)=2)*(J6:LR6<>0))/SUMPRODUCT((J6:LR6<>0)*(MOD(COLUMN(J6:LR6),4)=2))
If only blanks when
=SUMPRODUCT(J6:LR6*(MOD(COLUMN(J6:LR6),4)=2)*NOT(ISBLANK(J6:LR6)))/SUMPRODUCT(NOT(ISBLANK(J6:LR6))*(MOD(COLUMN(J6:LR6),4)=2))