Average

Copper Contributor

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?

7 Replies

@topcat999 

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.

 

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? 

@topcat999 

This is an example of a record list:xamDataPresenter_About_Fixed_Non_Scrolling_Records_01

 

 

Unique column names. Each column has the same data type (text, number, date, ...) in every row.

 

Thank you, @Detlef Lewin, got it! 

 

I am a 74 year-old "learner", as you might have already worked out!! 

@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))

 

Hi, @Sergei BaklanYou 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))