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

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

--
Don Guillett
SalesAid Software

"LostwithoutLost" wrote in
message ...
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

Thanks, will give it a go.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"LostwithoutLost" wrote in
message ...
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

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

"LostwithoutLost" wrote:

Thanks, will give it a go.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"LostwithoutLost" wrote in
message ...
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

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

=AVERAGE(IF(MOD(COLUMN(rng),3)=MOD(COLUMN(INDEX(rn g,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.



In article ,
LostwithoutLost wrote:

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

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),A 1:O1))

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

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

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

XL2002, WinXP


"LostwithoutLost" wrote:

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

Wow thanks guys, I will give them both a go and see. Thanks a lot !!

"JE McGimpsey" wrote:

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

=AVERAGE(IF(MOD(COLUMN(rng),3)=MOD(COLUMN(INDEX(rn g,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.



In article ,
LostwithoutLost wrote:

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

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)))
--
Don Guillett
SalesAid Software

"LostwithoutLost" wrote in
message ...
I gave it a go, and it worked, but it did not calculate every third entry,
how do I adapt it to do that.

"LostwithoutLost" wrote:

Thanks, will give it a go.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"LostwithoutLost" wrote in
message ...
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

Perfect. Thank you all very much. Job done !!!!!

"LostwithoutLost" wrote:

Wow thanks guys, I will give them both a go and see. Thanks a lot !!

"JE McGimpsey" wrote:

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

=AVERAGE(IF(MOD(COLUMN(rng),3)=MOD(COLUMN(INDEX(rn g,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.



In article ,
LostwithoutLost wrote:

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

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:C47 5,Details!C479:C632,Details!C636:C798

"JE McGimpsey" wrote:

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

=AVERAGE(IF(MOD(COLUMN(rng),3)=MOD(COLUMN(INDEX(rn g,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.



In article ,
LostwithoutLost wrote:

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