Apr 19 2019 09:47 AM
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 are not consecutive, viz: =(+J6+N6+R6+V6+Z6+AD6+AH6+AL6+AP6+AT6+AX6+BB6+BF6+BJ6+BN6+BR6+BV6+BZ6+CD6+CH6+CL6+CP6+CT6+CX6+DB6+DF6+DJ6+DN6+DR6+DV6+DZ6+ED6+EH6+EL6+EP6+ET6+EX6+FB6+FF6+FJ6+FN6+FR6+FV6+FZ6+GD6+GH6+GL6+GP6+GT6+GX6+HB6+HF6+HJ6+HN6+HR6+HV6+HZ6+ID6+IH6+IL6+IP6+IT6+IX6+JB6+JF6+JJ6+JN6+JR6+JV6+JZ6+KD6+KH6+KL6+KP6+KT6+KX6+LB6+LF6+LJ6+LN6+LR6)
If I try to obtain the average of all those cells, some of which are not populated (but will be over time) by adding to the above /COUNT(+J6+N6+R6+V6+Z6+AD6+AH6+AL6+AP6+AT6+AX6+BB6+BF6+BJ6+BN6+BR6+BV6+BZ6+CD6+CH6+CL6+CP6+CT6+CX6+DB6+DF6+DJ6+DN6+DR6+DV6+DZ6+ED6+EH6+EL6+EP6+ET6+EX6+FB6+FF6+FJ6+FN6+FR6+FV6+FZ6+GD6+GH6+GL6+GP6+GT6+GX6+HB6+HF6+HJ6+HN6+HR6+HV6+HZ6+ID6+IH6+IL6+IP6+IT6+IX6+JB6+JF6+JJ6+JN6+JR6+JV6+JZ6+KD6+KH6+KL6+KP6+KT6+KX6+LB6+LF6+LJ6+LN6+LR6)
all I get is the Sum of the cells.
Can you help me with this, please?
Apr 19 2019 10:07 AM
With COUNT(+J6+N6+R6+...) your adding up the values - becoming one value. And the count of one value is 1.
More appropriate would be COUNT(J6,N6,R6,...).
And you should change your model. It should be a data list. Calculating will be much more simple and efficient.
Apr 19 2019 10:18 AM
Thank you @Detlef Lewin !! Will definitely go with your suggestion!
I don't understand when you say to change my model to a data list. Could you please clarify on that?
Apr 19 2019 10:38 AM - edited Apr 19 2019 10:38 AM
This is an example of a record list:
Unique column names. Each column has the same data type (text, number, date, ...) in every row.
Apr 19 2019 10:40 AM
Thank you, @Detlef Lewin, got it!
I am a 74 year-old "learner", as you might have already worked out!!
Apr 19 2019 10:54 AM
@topcat999 , I didn't check all cells, but it looks like you aggregate every 4th column in the range. If so, average could be calculates as
=SUMPRODUCT(J6:LR6*(MOD(COLUMN(J6:LR6),4)=2))/SUMPRODUCT(--(MOD(COLUMN(J6:LR6),4)=2))
Apr 20 2019 04:31 AM
Hi, @Sergei Baklan. 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).
Apr 20 2019 05:08 AM
@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))