Average

%3CLINGO-SUB%20id%3D%22lingo-sub-475556%22%20slang%3D%22en-US%22%3EAverage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475556%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20establish%20the%20average%20value%20of%20a%20series%20of%20numbers%20in%20a%20string%20of%20individual%20cells%20in%20Excel.%20So%2C%20for%20example%20I%20have%20spreadsheet%20with%20whole%20number%20values%20in%20numerous%20separate%20cells%20that%20are%20not%20consecutive%2C%20viz%3A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3D(%2BJ6%2BN6%2BR6%2BV6%2BZ6%2BAD6%2BAH6%2BAL6%2BAP6%2BAT6%2BAX6%2BBB6%2BBF6%2BBJ6%2BBN6%2BBR6%2BBV6%2BBZ6%2BCD6%2BCH6%2BCL6%2BCP6%2BCT6%2BCX6%2BDB6%2BDF6%2BDJ6%2BDN6%2BDR6%2BDV6%2BDZ6%2BED6%2BEH6%2BEL6%2BEP6%2BET6%2BEX6%2BFB6%2BFF6%2BFJ6%2BFN6%2BFR6%2BFV6%2BFZ6%2BGD6%2BGH6%2BGL6%2BGP6%2BGT6%2BGX6%2BHB6%2BHF6%2BHJ6%2BHN6%2BHR6%2BHV6%2BHZ6%2BID6%2BIH6%2BIL6%2BIP6%2BIT6%2BIX6%2BJB6%2BJF6%2BJJ6%2BJN6%2BJR6%2BJV6%2BJZ6%2BKD6%2BKH6%2BKL6%2BKP6%2BKT6%2BKX6%2BLB6%2BLF6%2BLJ6%2BLN6%2BLR6)%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20try%20to%20obtain%20the%20average%20of%20all%20those%20cells%2C%20some%20of%20which%20are%20not%20populated%20(but%20will%20be%20over%20time)%20by%20adding%20to%20the%20above%20%2FCOUNT(%2BJ6%2BN6%2BR6%2BV6%2BZ6%2BAD6%2BAH6%2BAL6%2BAP6%2BAT6%2BAX6%2BBB6%2BBF6%2BBJ6%2BBN6%2BBR6%2BBV6%2BBZ6%2BCD6%2BCH6%2BCL6%2BCP6%2BCT6%2BCX6%2BDB6%2BDF6%2BDJ6%2BDN6%2BDR6%2BDV6%2BDZ6%2BED6%2BEH6%2BEL6%2BEP6%2BET6%2BEX6%2BFB6%2BFF6%2BFJ6%2BFN6%2BFR6%2BFV6%2BFZ6%2BGD6%2BGH6%2BGL6%2BGP6%2BGT6%2BGX6%2BHB6%2BHF6%2BHJ6%2BHN6%2BHR6%2BHV6%2BHZ6%2BID6%2BIH6%2BIL6%2BIP6%2BIT6%2BIX6%2BJB6%2BJF6%2BJJ6%2BJN6%2BJR6%2BJV6%2BJZ6%2BKD6%2BKH6%2BKL6%2BKP6%2BKT6%2BKX6%2BLB6%2BLF6%2BLJ6%2BLN6%2BLR6)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3Eall%20I%20get%20is%20the%20Sum%20of%20the%20cells.%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20with%20this%2C%20please%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-475556%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-475601%22%20slang%3D%22en-US%22%3ERe%3A%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324038%22%20target%3D%22_blank%22%3E%40topcat999%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%26nbsp%3B%3CSPAN%3ECOUNT(%2BJ6%2BN6%2BR6%2B...)%20your%20adding%20up%20the%20values%20-%20becoming%20one%20value.%20And%20the%20count%20of%20one%20value%20is%201.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMore%20appropriate%20would%20be%26nbsp%3BCOUNT(J6%2CN6%2CR6%2C...).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAnd%20you%20should%20change%20your%20model.%20It%20should%20be%20a%20data%20list.%20Calculating%20will%20be%20much%20more%20simple%20and%20efficient.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-475625%22%20slang%3D%22en-US%22%3ERe%3A%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475625%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B!!%20Will%20definitely%20go%20with%20your%20suggestion!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20understand%20when%20you%20say%20to%20change%20my%20model%20to%20a%20data%20list.%20Could%20you%20please%20clarify%20on%20that%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-475708%22%20slang%3D%22en-US%22%3ERe%3A%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324038%22%20target%3D%22_blank%22%3E%40topcat999%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20an%20example%20of%20a%20record%20list%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20609px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109633iD764E98225E0E748%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22xamDataPresenter_About_Fixed_Non_Scrolling_Records_01%22%20title%3D%22xamDataPresenter_About_Fixed_Non_Scrolling_Records_01%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnique%20column%20names.%20Each%20column%20has%20the%20same%20data%20type%20(text%2C%20number%2C%20date%2C%20...)%20in%20every%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-475719%22%20slang%3D%22en-US%22%3ERe%3A%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475719%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%2C%20got%20it!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%2074%20year-old%20%22learner%22%2C%20as%20you%20might%20have%20already%20worked%20out!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-475763%22%20slang%3D%22en-US%22%3ERe%3A%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475763%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324038%22%20target%3D%22_blank%22%3E%40topcat999%3C%2FA%3E%26nbsp%3B%2C%20I%20didn't%20check%20all%20cells%2C%20but%20it%20looks%20like%20you%20aggregate%20every%204th%20column%20in%20the%20range.%20If%20so%2C%20average%20could%20be%20calculates%20as%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(J6%3ALR6*(MOD(COLUMN(J6%3ALR6)%2C4)%3D2))%2FSUMPRODUCT(--(MOD(COLUMN(J6%3ALR6)%2C4)%3D2))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477252%22%20slang%3D%22en-US%22%3ERe%3A%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477252%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%26nbsp%3B%3CFONT%20face%3D%22%22%20size%3D%222%22%20color%3D%22mediumblue%22%3EYou%20are%20quite%20right%2C%20I%20have%20tried%20your%20suggestion%20by%20using%20your%20formula%20exactly%2C%20but%20it%20registers%20an%20answer%20of%20%220%22%3F%20when%2C%20in%20my%20test%2C%20I%20have%20populated%20a%20value%20of%2028%2C%2028%2C%20and%202%20in%20the%201st%2C%205th%20and%209th%20column%20of%20my%20model%2C%20which%20should%20give%20me%20an%20answer%20of%2019.333%20(i.e.%2058%2F3).%3C%2FFONT%3E%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EAny%20ideas%2C%20please%3F%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477272%22%20slang%3D%22en-US%22%3ERe%3A%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324038%22%20target%3D%22_blank%22%3E%40topcat999%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20we%20shall%20to%20exclude%20blanks.%20If%20exclude%20both%20zeroes%20and%20blanks%20when%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(J6%3ALR6*(MOD(COLUMN(J6%3ALR6)%2C4)%3D2)*(J6%3ALR6%26lt%3B%26gt%3B0))%2FSUMPRODUCT((J6%3ALR6%26lt%3B%26gt%3B0)*(MOD(COLUMN(J6%3ALR6)%2C4)%3D2))%3C%2FPRE%3E%0A%3CP%3EIf%20only%20blanks%20when%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(J6%3ALR6*(MOD(COLUMN(J6%3ALR6)%2C4)%3D2)*NOT(ISBLANK(J6%3ALR6)))%2FSUMPRODUCT(NOT(ISBLANK(J6%3ALR6))*(MOD(COLUMN(J6%3ALR6)%2C4)%3D2))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
topcat999
Occasional 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))

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies