Average 5 columns of data skipping blank columns

[marvin]

This is a repost that is hopefully clearer about what I'm
trying to accomplish.
I'm trying to average the last five columns in a row that
have a value. I must always have five(5)values to average
zero or blank must be skipped .

A B C D E F G H I J K L
day1 day2 day3 day4 day5 avg day6 day7 day8 day9 day10 avg
1 2
43 43 38 41 43 41.6 41 43 38 35
38 39 37 39 37 38 36 38 39

The formulas I'm using at this time;
=average(A2,B2,C2,D2,E2) for avg1
=average(G2,H2,I2,J2,K2) for avg2 (I must change formula
for each row if the desiginated columns are blank)

Using the above formula for avg2 means for each row I have
to change the formula to use the last five (5) columns
with actual data. What I need is a formula that would know
to use(k2,i2,h2,g2,e2) for the first example and
(k2,J2,G2,E2,D2)for the second example.

I need the avg2 and subsequent avg's formulas to use
the last five (5) columns with data.
With 120 rows of data changing the avg2 formula for
each row is a pain.

Thanks in advance
Marvin

 

[Ken Wright]

One way, assuming data in row 3 starting Col A and typically spanning 10 Columns
(Adjust ranges to suit)

=IF(COUNTIF($A3:$J3,">0")<5,"Less than 5
Numbers",AVERAGE(OFFSET($A3,,,,SMALL(IF(((A3:J3>0)*COLUMN(A3:J3))>0,(A3:J3>0)*CO
LUMN(A3:J3),""),5))))

array entered using CTRL+SHIFT+ENTER


If you know there will always be at least 5 numbers, then just the following:-

=AVERAGE(OFFSET($A3,,,,SMALL(IF(((A3:J3>0)*COLUMN(A3:J3))>0,(A3:J3>0)*COLUMN(A3:
J3),""),5)))

array entered using CTRL+SHIFT+ENTER

 

[Ken Wright]

Just looked at your question again, and the range seems to flip from left to
right, to right to left as you go through the note.

What ranges are the data in and what ranges are the average formulas to go in

 

[Marvin]

What I ultimately want to be able to do is start at the
field to the left of avg2 (K2)in my example and work back
untill I have five values to average. Right now I have it
setup to go left to right using just the standard formula.
The range for avg2 if all fields have data should be K2-G2
if anyone of those fields are blank then it would go back
to the next field with a value. I tried to show this in
the examples. It could turn out that avg2 would actually
use the first 5 columns of data E2,D2,C2,B2,A2, for it's
average. Hope this answers your question.
Thanks
Marvin

 

[Ken Wright]

Try the following in K2 and copy down:-

=AVERAGE(OFFSET($J2,,,,SMALL(IF((($A2:$J2>0)*COLUMN($A2:$J2))>0,($A2:$J2>0)*COLU
MN($A2:$J2),""),COUNTIF($A2:$J2,">0")-4)-11))

 

[Ken Wright]

No error checking included for if it falls below 5 values though - Do you need
it?

 

[marvin]

It would be nice If I had something to put
into the avg field like " INC " if
there was less than five values to average.

Marvin

 

[Ken Wright]

=IF(COUNT($A2:$J2)<5,"INC",AVERAGE(OFFSET($J2,,,,SMALL(IF((($A2:$J2>0)*COLUMN($A
2:$J2))>0,($A2:$J2>0)*COLU
MN($A2:$J2),""),COUNTIF($A2:$J2,">0")-4)-11)))

 

[Ken Wright]

Must be array entered using CTRL+SHIFT+ENTER

 

[marvin]

Ken,
Thanks so very much for all the help. It looks like
this is going to work just fine. I will have to move
a few colums but that is minor. This will be much
better than changing each avg formula, which was
about four hundred formula changes.
Marvin

-----Original Message-----
=IF(COUNT($A2:$J2)<5,"INC",AVERAGE(OFFSET($J2,,,,SMALL(IF ((($A2:$J2>0)*COLUMN($A
2:$J2))>0,($A2:$J2>0)*COLU
MN($A2:$J2),""),COUNTIF($A2:$J2,">0")-4)-11)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission
---------------------------------------------------------- ------------------

It would be nice If I had something to put
into the avg field like " INC " if
there was less than five values to average.

Marvin

-----Original Message-----
No error checking included for if it falls below 5

values
though - Do you need

it?

MVP -
Excel

Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------

---
------------------

It's easier to beg forgiveness than ask permission
-------------------------------------------------------

---
------------------

Try the following in K2 and copy down:-


=AVERAGE(OFFSET($J2,,,,SMALL(IF((($A2:$J2>0)*COLUMN ($A2:$J2))>0,($A2:$J2>0)*COLU
MN($A2:$J2),""),COUNTIF($A2:$J2,">0")-4)-11))

MVP -
Excel

Sys Spec - Win XP Pro / XL 00/02/03

-----------------------------------------------------

---
--------------------

It's easier to beg forgiveness than ask permission :- )
-----------------------------------------------------

---
--------------------

What I ultimately want to be able to do is start

at
the

field to the left of avg2 (K2)in my example and

work
back

untill I have five values to average. Right now I have it
setup to go left to right using just the standard formula.
The range for avg2 if all fields have data should

be
K2-G2

if anyone of those fields are blank then it would

go
back

to the next field with a value. I tried to show

this
in

the examples. It could turn out that avg2 would actually
use the first 5 columns of data E2,D2,C2,B2,A2,

for
it's

average. Hope this answers your question.
Thanks
Marvin

-----Original Message-----
Just looked at your question again, and the range seems
to flip from left to
right, to right to left as you go through the note.

What ranges are the data in and what ranges are the
average formulas to go in

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------

---
----- permission :-
)

--------------------------------------------------

---
-----

------------------





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date:
16/01/2004


.

Date:
16/01/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004


.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004


.

 

[Ken Wright]

My pleasure, and thanks for letting me know. If you have any problems adjusting
the formula then just post back.