Average every third entry

L

LostwithoutLost

Hi there, thanks for all your help in advance. I am looking for a formula
that will average every 3rd entry on a row of data. I would like to set it
up so that it ignores empty cells. chris
 
D

Don Guillett

Use this array formula(must be entered using ctrl+shift+enter) to average <>
" "
=AVERAGE(IF(H2:H10<>" ",H2:H10))
 
L

LostwithoutLost

Thanks, will give it a go.

Don Guillett said:
Use this array formula(must be entered using ctrl+shift+enter) to average <>
" "
=AVERAGE(IF(H2:H10<>" ",H2:H10))
Click to expand...
 
L

LostwithoutLost

I gave it a go, and it worked, but it did not calculate every third entry,
how do I adapt it to do that.
 
J

JE McGimpsey

One way (array-entered:CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(MOD(COLUMN(rng),3)=MOD(COLUMN(INDEX(rng,1)),3),
IF(rng<>"", rng)))

where "rng" is the range of data in your row. This assumes you want the
1st, 4th, 7th... entry.

If you want the 2nd, 5th, 8th...entry, change INDEX(rng,1) to
INDEX(rng,2), etc.
 
R

Ron Coderre

Try this:

With
A1:O1 containing numbers or blanks

These formulas average every 3rd non-blank cell,

beginning with cell A1
A2: =AVERAGE(IF((A1:O1<>"")*(MOD(COLUMN(A1:O1),3)=1),A1:O1))

beginning with cell B1
A2: =AVERAGE(IF((A1:O1<>"")*(MOD(COLUMN(A1:O1),3)=2),A1:O1))

beginning with cell C1
A2: =AVERAGE(IF((A1:O1<>"")*(MOD(COLUMN(A1:O1),3)=0),A1:O1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
D

Don Guillett

try this
=AVERAGE(IF(MOD(ROW(H2:H10)-1,3)=0,H2:H10))
or
=AVERAGE(IF(MOD(ROW($H$2:$H$10),3)=1,IF(ISNUMBER($H$2:$H$10)<>"",$H$2:$H$10)))
 
S

Sarah

I would like to use this formula, however is there a way to alter it so I
take the average of every 3rd number in multiple ranges?

ex: take every 3rd number in the following ranges
Details!C8:C161,Details!C165:C318,Details!C322:C475,Details!C479:C632,Details!C636:C798
 
Top