I need to know only the last 4 numbers, but these change weekly.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:B1000),IF(B1:B1000>0,ROW(B1:B1000))),MIN(4,COUNTIF(B1:B1000,">0"))),IF(B1:B1000>0,B1:B1000)))

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

Mike
 
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(LARGE((B1:B28<>"")*ROW(B1:B28),4),0))),0)

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

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)
 
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
 
Simpler than mine but can it be modified to give the correct result if
there
is a non-numeric value in the range?
Click to expand...

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(LARGE(ISNUMBER(B1:B28)*ROW(B1:B28),4),0))),0)

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)
 
=IF(COUNT(B1:B28),AVERAGE(B28:INDEX(B1:B28,INDEX(LARGE((B1:B28<>"")*(ISNUMBER(B1:B28))*ROW(B1:B28),4),0))),0)
 
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
 
Mike
Icant thank you enough, it works a treat.

Mike H said:
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:B1000),IF(B1:B1000>0,ROW(B1:B1000))),MIN(4,COUNTIF(B1:B1000,">0"))),IF(B1:B1000>0,B1:B1000)))

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

Mike
Click to expand...
 
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)
 
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.
 
Back
Top