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?
--
Sandi Gardner

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2: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(((B 2: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


"Sandi" wrote:

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?
--
Sandi Gardner

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


"Ron Coderre" wrote:

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2: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(((B 2: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


"Sandi" wrote:

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?
--
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(((A 1: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(((A 1: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


"Sandi" wrote:

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


"Ron Coderre" wrote:

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2: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(((B 2: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


"Sandi" wrote:

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?
--
Sandi Gardner

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


"Ron Coderre" wrote:

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(((A 1: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(((A 1: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


"Sandi" wrote:

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


"Ron Coderre" wrote:

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2: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(((B 2: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


"Sandi" wrote:

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?
--
Sandi Gardner

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

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

XL2002, WinXP-Pro


"Sandi" wrote:

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


"Ron Coderre" wrote:

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(((A 1: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(((A 1: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


"Sandi" wrote:

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


"Ron Coderre" wrote:

Try something like this:

For your data in Cells B2:L2

A2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2: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(((B 2: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


"Sandi" wrote:

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?
--
Sandi Gardner