Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula - Average from every other cell
Hi,
I have data in A2:A325, A2,A4,A6 etc represents length and A3,A5,A7 etc represents breadth. I am trying to calculate average length and average breadth, so I want to just average A2,A4,A6 etc and seperately want to average A3,A5,A7 etc. I've tried using the array formulas below but with little success, can anyone point out where I'm going wrong or offer an alternative? =AVERAGE(IF(MOD(A2:A324,2),"",A2:A324)) gives #DIV/0! =AVERAGE(IF(MOD(A3:A325,2),A3:A325,"")) gives an answer I'm not sure is correct Thanks, Oscar. PS. I have used [Ctrl][Shift][Enter] to get {around array} |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula - Average from every other cell
=AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,0))
=AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,0)) both array entered -- Gary''s Student - gsnu200792 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula - Average from every other cell
Absolutely terrific, that's nailed it!
Thanks a million Gary's Student, Oscar "Gary''s Student" wrote: =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,0)) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,0)) both array entered -- Gary''s Student - gsnu200792 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula - Average from every other cell
You are very welcome!
-- Gary''s Student - gsnu200792 "Oscar Munero" wrote: Absolutely terrific, that's nailed it! Thanks a million Gary's Student, Oscar "Gary''s Student" wrote: =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,0)) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,0)) both array entered -- Gary''s Student - gsnu200792 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula - Average from every other cell
Doesn't that throw in a lot of zeroes to the average, both for the alternate
rows and for any blank input cells? It doesn't seem to give the right answer for me. What does seem to give the right answer (on limited testing) is =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,IF(A1:A325="","" ,A1:A325),"")) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,IF(A1:A325="","" ,A1:A325),"")) both array entered. -- David Biddulph "Gary''s Student" wrote in message ... =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,0)) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,0)) both array entered -- Gary''s Student - gsnu200792 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula - Average from every other cell
You are correct!!
I forgot that AVERAGE ignores blanks, not zeros. My formulas should have been: =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,"")) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,"")) Thanks for the correction! -- Gary''s Student - gsnu200792 "David Biddulph" wrote: Doesn't that throw in a lot of zeroes to the average, both for the alternate rows and for any blank input cells? It doesn't seem to give the right answer for me. What does seem to give the right answer (on limited testing) is =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,IF(A1:A325="","" ,A1:A325),"")) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,IF(A1:A325="","" ,A1:A325),"")) both array entered. -- David Biddulph "Gary''s Student" wrote in message ... =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,0)) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,0)) both array entered -- Gary''s Student - gsnu200792 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula - Average from every other cell
Doesn't your formula still have problems where there are blank cells in the
input range? Doesn't your formula effectively turn those into zeroes? That's why I had my additional test for blank inputs. -- David Biddulph "Gary''s Student" wrote in message ... You are correct!! I forgot that AVERAGE ignores blanks, not zeros. My formulas should have been: =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,"")) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,"")) Thanks for the correction! -- Gary''s Student - gsnu200792 "David Biddulph" wrote: Doesn't that throw in a lot of zeroes to the average, both for the alternate rows and for any blank input cells? It doesn't seem to give the right answer for me. What does seem to give the right answer (on limited testing) is =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,IF(A1:A325="","" ,A1:A325),"")) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,IF(A1:A325="","" ,A1:A325),"")) both array entered. -- David Biddulph "Gary''s Student" wrote in message ... =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,0)) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,0)) both array entered -- Gary''s Student - gsnu200792 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula - Average from every other cell
Hi Guys,
I tried both your suggestions (Gary''s Students' amended version) and I'm getting the same answer for average from each method. I can see where David Biddulph is coming from though, if I extend the range to include e.g. A1:A330 (5 blank cells) then the 2 suggested arrays begin to return different average answers. Luckily my original data set has neither blanks nor zeroes, so both methods work just fine. Thanks to you both for increasing my understanding of how arrays work, Oscar. "David Biddulph" wrote: Doesn't your formula still have problems where there are blank cells in the input range? Doesn't your formula effectively turn those into zeroes? That's why I had my additional test for blank inputs. -- David Biddulph "Gary''s Student" wrote in message ... You are correct!! I forgot that AVERAGE ignores blanks, not zeros. My formulas should have been: =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,"")) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,"")) Thanks for the correction! -- Gary''s Student - gsnu200792 "David Biddulph" wrote: Doesn't that throw in a lot of zeroes to the average, both for the alternate rows and for any blank input cells? It doesn't seem to give the right answer for me. What does seem to give the right answer (on limited testing) is =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,IF(A1:A325="","" ,A1:A325),"")) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,IF(A1:A325="","" ,A1:A325),"")) both array entered. -- David Biddulph "Gary''s Student" wrote in message ... =AVERAGE(IF(MOD(ROW(A1:A325),2)=0,A1:A325,0)) =AVERAGE(IF(MOD(ROW(A1:A325),2)=1,A1:A325,0)) both array entered -- Gary''s Student - gsnu200792 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average If Array Formula | Excel Worksheet Functions | |||
Array Formula to find Average Return | Excel Discussion (Misc queries) | |||
Average Array help with a formula | Excel Worksheet Functions | |||
Array Formula to Pick Average | Excel Discussion (Misc queries) | |||
Average Array Formula | Excel Worksheet Functions |