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

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,LAR GE(IF(A1:J1<"",COLUMN(A1:J1)),5))))

--
Regards,

Peo Sjoblom

Portland, Oregon




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

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


"Peo Sjoblom" wrote:

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,LAR GE(IF(A1:J1<"",COLUMN(A1:J1)),5))))

--
Regards,

Peo Sjoblom

Portland, Oregon




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