Average Function that Counts

[Guest]

I have a worksheet that is layed out as so:

A B C D E F G H I J
Average
1 25 26 13 17 26
2 33 26 32 25 15 65 17 25
3 45 61 33 12 42

What I am needing is a formula that will look at a single row and starting
from Right to Left find the last five numbers in my row and average them out.
There will be some rows that have blank info but would need it to skip the
blank cells to locate the first cell with a numerical entry. Is there such a
formual?

 

[Guest]

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/5

or
If there may be less than 5 entries
A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<>0)))

Note: Commit both of those array formulas by holding down the [Ctrl][Shift]
keys and press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Hi Ron - Thanks for the reply! I tried what you listed and unfortunately
that didn't work. I keep getting 0's for my answer.

I need for excel to read the rows from right to left and find the first 5
numerical entries that it comes to, add those numbers together and take an
average of those numbers.

Any help would be appreciated. Thanks so much
--
Sandi Gardner

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/5

or
If there may be less than 5 entries
A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<>0)))

Note: Commit both of those array formulas by holding down the [Ctrl][Shift]
keys and press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have a worksheet that is layed out as so:

A B C D E F G H I J
Average
1 25 26 13 17 26
2 33 26 32 25 15 65 17 25
3 45 61 33 12 42

What I am needing is a formula that will look at a single row and starting
from Right to Left find the last five numbers in my row and average them out.
There will be some rows that have blank info but would need it to skip the
blank cells to locate the first cell with a numerical entry. Is there such a
formual?

 

[Guest]

Try this variation, which is more tailored to the example you posted:

For sproradic values in A1:J1
K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/5

Or, again, if there may be less than 5 items and you want the average of the
available values:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<>0)))

Note: Commit those array formulas by holding down the [Ctrl][Shift] keys and
press [Enter].

Using the data you posted, those formulas return:
K1: 21.4
K2: 29.4
K3: 38.6

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi Ron - Thanks for the reply! I tried what you listed and unfortunately
that didn't work. I keep getting 0's for my answer.

I need for excel to read the rows from right to left and find the first 5
numerical entries that it comes to, add those numbers together and take an
average of those numbers.

Any help would be appreciated. Thanks so much
--
Sandi Gardner

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/5

or
If there may be less than 5 entries
A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<>0)))

Note: Commit both of those array formulas by holding down the [Ctrl][Shift]
keys and press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

I have a worksheet that is layed out as so:

A B C D E F G H I J
Average
1 25 26 13 17 26
2 33 26 32 25 15 65 17 25
3 45 61 33 12 42

What I am needing is a formula that will look at a single row and starting
from Right to Left find the last five numbers in my row and average them out.
There will be some rows that have blank info but would need it to skip the
blank cells to locate the first cell with a numerical entry. Is there such a
formual?

 

[Guest]

Ron - you're a genious - worked perfectly!!!! Thanks so much!!!!
--
Sandi Gardner

Try this variation, which is more tailored to the example you posted:

For sproradic values in A1:J1
K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/5

Or, again, if there may be less than 5 items and you want the average of the
available values:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<>0)))

Note: Commit those array formulas by holding down the [Ctrl][Shift] keys and
press [Enter].

Using the data you posted, those formulas return:
K1: 21.4
K2: 29.4
K3: 38.6

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi Ron - Thanks for the reply! I tried what you listed and unfortunately
that didn't work. I keep getting 0's for my answer.

I need for excel to read the rows from right to left and find the first 5
numerical entries that it comes to, add those numbers together and take an
average of those numbers.

Any help would be appreciated. Thanks so much
--
Sandi Gardner

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/5

or
If there may be less than 5 entries
A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<>0)))

Note: Commit both of those array formulas by holding down the [Ctrl][Shift]
keys and press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

I have a worksheet that is layed out as so:

A B C D E F G H I J
Average
1 25 26 13 17 26
2 33 26 32 25 15 65 17 25
3 45 61 33 12 42

What I am needing is a formula that will look at a single row and starting
from Right to Left find the last five numbers in my row and average them out.
There will be some rows that have blank info but would need it to skip the
blank cells to locate the first cell with a numerical entry. Is there such a
formual?

 

[Guest]

Thanks for the update.
I'm glad that worked for you, Sandi.

***********
Regards,
Ron

XL2002, WinXP-Pro

Ron - you're a genious - worked perfectly!!!! Thanks so much!!!!
--
Sandi Gardner

Try this variation, which is more tailored to the example you posted:

For sproradic values in A1:J1
K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/5

Or, again, if there may be less than 5 items and you want the average of the
available values:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<>0)))

Note: Commit those array formulas by holding down the [Ctrl][Shift] keys and
press [Enter].

Using the data you posted, those formulas return:
K1: 21.4
K2: 29.4
K3: 38.6

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi Ron - Thanks for the reply! I tried what you listed and unfortunately
that didn't work. I keep getting 0's for my answer.

I need for excel to read the rows from right to left and find the first 5
numerical entries that it comes to, add those numbers together and take an
average of those numbers.

Any help would be appreciated. Thanks so much
--
Sandi Gardner


:

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/5

or
If there may be less than 5 entries
A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<>0)))

Note: Commit both of those array formulas by holding down the [Ctrl][Shift]
keys and press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

I have a worksheet that is layed out as so:

A B C D E F G H I J
Average
1 25 26 13 17 26
2 33 26 32 25 15 65 17 25
3 45 61 33 12 42

What I am needing is a formula that will look at a single row and starting
from Right to Left find the last five numbers in my row and average them out.
There will be some rows that have blank info but would need it to skip the
blank cells to locate the first cell with a numerical entry. Is there such a
formual?

 

[Peo Sjoblom]

A shorter version

=AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<>"",COLUMN(A1:J1)),5)))

array entered, if less than 5 entries

=IF(COUNT(
A1:J1)<5,AVERAGE(A1:J1),AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<>"",COLUMN(A1:J1)),5))))

--
Regards,

Peo Sjoblom

Portland, Oregon

Thanks for the update.
I'm glad that worked for you, Sandi.

***********
Regards,
Ron

XL2002, WinXP-Pro

Ron - you're a genious - worked perfectly!!!! Thanks so much!!!!
--
Sandi Gardner

Try this variation, which is more tailored to the example you posted:

For sproradic values in A1:J1
K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/5

Or, again, if there may be less than 5 items and you want the average
of the
available values:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<>0)))

Note: Commit those array formulas by holding down the [Ctrl][Shift]
keys and
press [Enter].

Using the data you posted, those formulas return:
K1: 21.4
K2: 29.4
K3: 38.6

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Hi Ron - Thanks for the reply! I tried what you listed and
unfortunately
that didn't work. I keep getting 0's for my answer.

I need for excel to read the rows from right to left and find the
first 5
numerical entries that it comes to, add those numbers together and
take an
average of those numbers.

Any help would be appreciated. Thanks so much
--
Sandi Gardner


:

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/5

or
If there may be less than 5 entries
A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<>0)))

Note: Commit both of those array formulas by holding down the
[Ctrl][Shift]
keys and press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

I have a worksheet that is layed out as so:

A B C D E F G H I
J
Average
1 25 26 13 17 26
2 33 26 32 25 15 65 17 25
3 45 61 33 12 42

What I am needing is a formula that will look at a single row and
starting
from Right to Left find the last five numbers in my row and
average them out.
There will be some rows that have blank info but would need it
to skip the
blank cells to locate the first cell with a numerical entry. Is
there such a
formual?

 

[Guest]

Thanks, Peo...those are better.

And building on your approach...this one, which includes the option for less
than 5 items, is even shorter:

=AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<>"",COLUMN(A1:J1)),MIN(COUNT(A1:J1),5))))

I'll be saving your first one and this one for futue use
(until somebody comes up with better ones!)

***********
Best Regards,
Ron

XL2002, WinXP-Pro

A shorter version

=AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<>"",COLUMN(A1:J1)),5)))

array entered, if less than 5 entries

=IF(COUNT(
A1:J1)<5,AVERAGE(A1:J1),AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<>"",COLUMN(A1:J1)),5))))

--
Regards,

Peo Sjoblom

Portland, Oregon

Thanks for the update.
I'm glad that worked for you, Sandi.

***********
Regards,
Ron

XL2002, WinXP-Pro

Ron - you're a genious - worked perfectly!!!! Thanks so much!!!!
--
Sandi Gardner


:

Try this variation, which is more tailored to the example you posted:

For sproradic values in A1:J1
K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/5

Or, again, if there may be less than 5 items and you want the average
of the
available values:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1:J1<>0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}),0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<>0)))

Note: Commit those array formulas by holding down the [Ctrl][Shift]
keys and
press [Enter].

Using the data you posted, those formulas return:
K1: 21.4
K2: 29.4
K3: 38.6

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Hi Ron - Thanks for the reply! I tried what you listed and
unfortunately
that didn't work. I keep getting 0's for my answer.

I need for excel to read the rows from right to left and find the
first 5
numerical entries that it comes to, add those numbers together and
take an
average of those numbers.

Any help would be appreciated. Thanks so much
--
Sandi Gardner


:

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/5

or
If there may be less than 5 entries
A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B2:L2<>0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}),0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<>0)))

Note: Commit both of those array formulas by holding down the
[Ctrl][Shift]
keys and press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

I have a worksheet that is layed out as so:

A B C D E F G H I
J
Average
1 25 26 13 17 26
2 33 26 32 25 15 65 17 25
3 45 61 33 12 42

What I am needing is a formula that will look at a single row and
starting
from Right to Left find the last five numbers in my row and
average them out.
There will be some rows that have blank info but would need it
to skip the
blank cells to locate the first cell with a numerical entry. Is
there such a
formual?