I have a spreadsheet which gives me the following information, if there is no
data insert 0 - This is ok and works. The next bit asks if there are less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the bottom of
my column, and work back up, identifying the first 4 cells with data in them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six empty
rows followed by 500, a blank and then 650. So the formula would find 650,
500,900,700, returning for me at the top of the sheet the figure 687.5 - help
anyone

Hi,

Let's say your data are in the range B1 - B1000. This will give the average
of the bottom 4 numeric cells:-

=AVERAGE(IF(ROW(B1:B1000)=LARGE(IF(ISNUMBER(B1:B1 000),IF(B1:B10000,ROW(B1:B1000))),MIN(4,COUNTIF(B 1:B1000,"0"))),IF(B1:B10000,B1:B1000)))

It's an array so enter with Ctrl+Shift+Enter

Mike

"dartanion" wrote:

I have a spreadsheet which gives me the following information, if there is no
data insert 0 - This is ok and works. The next bit asks if there are less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the bottom of
my column, and work back up, identifying the first 4 cells with data in them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six empty
rows followed by 500, a blank and then 650. So the formula would find 650,
500,900,700, returning for me at the top of the sheet the figure 687.5 - help
anyone

Try something like this

This regular formula returns the average of up to the last 4 numbers in the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there is
no
data insert 0 - This is ok and works. The next bit asks if there are less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find 650,
500,900,700, returning for me at the top of the sheet the figure 687.5 -
help
anyone

Ron,

Simpler than mine but can it be modified to give the correct result if there
is a non-numeric value in the range? My understanding is that this formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers in the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there is
no
data insert 0 - This is ok and works. The next bit asks if there are less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find 650,
500,900,700, returning for me at the top of the sheet the figure 687.5 -
help
anyone

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range?

It depends on the user's needs. In the posted example, there were numbers
and blanks....no text. Consequently, I tailored the solution to that
scenario.

If text may sneak into the range, this adjusted version works:
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE(ISNUMBER(B1:B28)*ROW(B1:B28),4),0))),0)

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)


"Mike H" wrote in message
...
Ron,

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range? My understanding is that this formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers in
the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there
is
no
data insert 0 - This is ok and works. The next bit asks if there are
less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the
bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find
650,
500,900,700, returning for me at the top of the sheet the figure
687.5 -
help
anyone

=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*(ISNUMBER(B1:B28))*ROW(B1:B28),4 ),0))),0)


--
Regards,

Peo Sjoblom



"Mike H" wrote in message
...
Ron,

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range? My understanding is that this formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers in
the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there
is
no
data insert 0 - This is ok and works. The next bit asks if there are
less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the
bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find
650,
500,900,700, returning for me at the top of the sheet the figure
687.5 -
help
anyone

Ron,

Thank you for that. I wasn't questioning the accuracy of your reply to the
OP, I just wanted to know whether the non-array solution could cope with text
in the range.

Mike

"Ron Coderre" wrote:

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range?

It depends on the user's needs. In the posted example, there were numbers
and blanks....no text. Consequently, I tailored the solution to that
scenario.

If text may sneak into the range, this adjusted version works:
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE(ISNUMBER(B1:B28)*ROW(B1:B28),4),0))),0)

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)


"Mike H" wrote in message
...
Ron,

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range? My understanding is that this formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers in
the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there
is
no
data insert 0 - This is ok and works. The next bit asks if there are
less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the
bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find
650,
500,900,700, returning for me at the top of the sheet the figure
687.5 -
help
anyone

Ron thanks, but it brought an error message up,. The reply from Mike worked
perfectly

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers in the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there is
no
data insert 0 - This is ok and works. The next bit asks if there are less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find 650,
500,900,700, returning for me at the top of the sheet the figure 687.5 -
help
anyone

Thanks very much, your help is much appreciated. The one from Mike worked
perfectly.

"Peo Sjoblom" wrote:

=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*(ISNUMBER(B1:B28))*ROW(B1:B28),4 ),0))),0)


--
Regards,

Peo Sjoblom



"Mike H" wrote in message
...
Ron,

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range? My understanding is that this formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers in
the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there
is
no
data insert 0 - This is ok and works. The next bit asks if there are
less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the
bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find
650,
500,900,700, returning for me at the top of the sheet the figure
687.5 -
help
anyone

Mike
Icant thank you enough, it works a treat.

"Mike H" wrote:

Hi,

Let's say your data are in the range B1 - B1000. This will give the average
of the bottom 4 numeric cells:-

=AVERAGE(IF(ROW(B1:B1000)=LARGE(IF(ISNUMBER(B1:B1 000),IF(B1:B10000,ROW(B1:B1000))),MIN(4,COUNTIF(B 1:B1000,"0"))),IF(B1:B10000,B1:B1000)))

It's an array so enter with Ctrl+Shift+Enter

Mike

"dartanion" wrote:

I have a spreadsheet which gives me the following information, if there is no
data insert 0 - This is ok and works. The next bit asks if there are less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the bottom of
my column, and work back up, identifying the first 4 cells with data in them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six empty
rows followed by 500, a blank and then 650. So the formula would find 650,
500,900,700, returning for me at the top of the sheet the figure 687.5 - help
anyone

I didn't think you were questioning the accuracy, Mike. It was a good
question and prompted me to tweak the formula so it avoids being tripped up
by inadvertent text in the range. Thanks.

Best Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)



"Mike H" wrote in message
...
Ron,

Thank you for that. I wasn't questioning the accuracy of your reply to the
OP, I just wanted to know whether the non-array solution could cope with
text
in the range.

Mike

"Ron Coderre" wrote:

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range?

It depends on the user's needs. In the posted example, there were
numbers
and blanks....no text. Consequently, I tailored the solution to that
scenario.

If text may sneak into the range, this adjusted version works:
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE(ISNUMBER(B1:B28)*ROW(B1:B28),4),0))),0)

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)


"Mike H" wrote in message
...
Ron,

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range? My understanding is that this
formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers
in
the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if
there
is
no
data insert 0 - This is ok and works. The next bit asks if there are
less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the
bottom
of
my column, and work back up, identifying the first 4 cells with data
in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty
rows
followed by cells with 400, 700, a couple of blanks then 900 then
six
empty
rows followed by 500, a blank and then 650. So the formula would
find
650,
500,900,700, returning for me at the top of the sheet the figure
687.5 -
help
anyone

Just tried it out, and used the following series 89, 90, 90, 76, 76, 76, 76
expecting the answer to be 76 but got 81.1. But many thanks for the try.

"Peo Sjoblom" wrote:

=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*(ISNUMBER(B1:B28))*ROW(B1:B28),4 ),0))),0)


--
Regards,

Peo Sjoblom



"Mike H" wrote in message
...
Ron,

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range? My understanding is that this formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers in
the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if there
is
no
data insert 0 - This is ok and works. The next bit asks if there are
less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the
bottom
of
my column, and work back up, identifying the first 4 cells with data in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty rows
followed by cells with 400, 700, a couple of blanks then 900 then six
empty
rows followed by 500, a blank and then 650. So the formula would find
650,
500,900,700, returning for me at the top of the sheet the figure
687.5 -
help
anyone

Really? I got 76. Btw, it's not a try, I know it works


--
Regards,

Peo Sjoblom



"dartanion" wrote in message
...
Just tried it out, and used the following series 89, 90, 90, 76, 76, 76,
76
expecting the answer to be 76 but got 81.1. But many thanks for the try.

"Peo Sjoblom" wrote:

=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*(ISNUMBER(B1:B28))*ROW(B1:B28),4 ),0))),0)


--
Regards,

Peo Sjoblom



"Mike H" wrote in message
...
Ron,

Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range? My understanding is that this
formula
takes the last 4 values in a range (not necessarily the last 4 numeric
values) and averages them if they are numeric.

Mike

"Ron Coderre" wrote:

Try something like this

This regular formula returns the average of up to the last 4 numbers
in
the
range B1:B28.
(If there are no numbers, it returns zero):
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(L ARGE((B1:B28<"")*ROW(B1:B28),4),0))),0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

"dartanion" wrote in message
...
I have a spreadsheet which gives me the following information, if
there
is
no
data insert 0 - This is ok and works. The next bit asks if there are
less
than 5 entries, what is the average, again this formula works ok.
The next bit completely baffles me. All I want is to start at the
bottom
of
my column, and work back up, identifying the first 4 cells with data
in
them,
add them together and give me the average.
eg column b has ten empty rows, then the number 600, then 3 empty
rows
followed by cells with 400, 700, a couple of blanks then 900 then
six
empty
rows followed by 500, a blank and then 650. So the formula would
find
650,
500,900,700, returning for me at the top of the sheet the figure
687.5 -
help
anyone