Offset formula

  • Thread starter Thread starter Nan
  • Start date Start date
N

Nan

I have a spreadsheet that is updated on a weekly basis and I have
summary of the information at the bottom of the page. I only want t
show the last figure in column X. I've been using the followin
formula....

OFFSET($X$5,COUNTA($X5:$X56)-1,0)

This has worked until I put a formula in column X (the user used t
have to fill this in as they went). Now my OFFSET formula isn'
working. Is it because I now have an underlying formula there?

I have this formula in column X so the user only has to enter the dat
in column W and column X calculates for them.

IF(ISBLANK(W14),"",SUM(X13+W14))

Can anyone help?
 
Hi
the reason for this is that COUNTA counts the formula result "" also as
a non blank cell. Try the following instead to get the last numeric
value in your range:
=VLOOKUP(9.9999999E307,$X5:$X100)
 
Hi Frank,

I tried the VLOOKUP formula below but it's telling me that I've entere
too few arguments.

Thanks,
Na
 
Frank meant to type LOOKUP(... not VLOOKUP(....
His coffee supply is running low as it is early evening in Germany now. Or
has he already switched to bier?
 
Thanks, Frank, it works!!

I now have another, related question. Column X is calculating
cumulative total. I want to be able to calculate the average weekl
total. I was using this formula

=OFFSET($x,COUNTA($x5:$x56)-1,0)/COUNTA($x5:$x56)

So I'm trying to take the result of your VLOOKUP formula and divide i
by the total number of weeks for which we have data. Can I do somethin
like that with the VLOOKUP?

Thanks, Na
 
Hi
untested but try
=VLOOKUP(9.9999999E307,$X5:$X100,1)/SUMPRODUCT(--($X$5:$X$100<>""))
 
Bernard said:
Frank meant to type LOOKUP(... not VLOOKUP(....
His coffee supply is running low as it is early evening in Germany
now. Or has he already switched to bier?
Click to expand...

Bernard
lol
no it's just Coke :-)
Frank
 
Back
Top