What if the last value is zero?

  • Thread starter Thread starter Zadig Galbaras
  • Start date Start date
Z

Zadig Galbaras

Hi folks!

I need help.

I have two columns A and B.
A column of and F is a sum of a few other cells i columns B,C ans D

Then I have a cell lets say its B150 looking of the last value in B
That is if the value in A is smaller than TODAY()

The funtion in C1 looks like this:
=INDEX(B2:B54;COMPARE(9,99999999999999E+307;B2:B154))
That is, the date of each saturday during a year.

The problem is that this do not give me any zero values in B150. Only the
last value in the B-column which is greater than zero. And this could be
last weeks value in the B-column, not todays.

Is there a way to solve this?

If you didn't understand please alborate, I really need this fixed.
 
Hi
I think you translated your formula from a non-English version as the
function COMPARE does not exist and you're using the ';' as function
delimiter.
If you want to get the last filled cell within a range there are some
ways:
one way: If you're only looking for numbers, try
=INDEX(B2:B54,IF(ISNUMBER(B2:B154),ROW(B2:B154),0)-1,1)
entered as array formula (CTRL+SHIFT+ENTER)

HTH
Frank
 
Hi
sorry that was to fast. Use
=INDEX(B2:B54,MAX(IF(ISNUMBER(B2:B154),ROW(B2:B154),0))-1,1)

Frank
 
It's a bit unclear what the problem is...

You have B2:B54 for INDEX and B2:B154 for COMPARE. These ranges should be
same.

But, if COMPARE means MATCH...

=INDEX(B2:B154;COMPARE(9,99999999999999E+307;B2:B154))

will give you the last numeric value from B2:B154 even when the last numeric
value is a real 0.
 
I thnk you for your help....but.
Yes you're right about me translating this function. I use a norwegian
version of Excel.
Now I have to find somewhere on the net where the english functions are
listed with their norwegian equivalent
--ZG--
 
Yes COMPARE is supposed to be MATCH.

But the formula picks the last number in the row, except if its a zero. Then
it picks the last number larger than zero.

LEt me explain again, and now a lot clearer.

Cells A2:A54 contains dates.
Cells B2:B54 contains numbers.
The cell C1 is going to show the last number in the B2:B54 list

The formula in C1 is
=INDEX(B2:B54;MATCH(9,99999999999999E+307;B2:B54))

The formula in B2:B54 is a standard IF() sentence which sets a zero in the
cell if it false, and the sum of three numbers if true, else a zero.

I hope this maeks it clearer.... :-)

Appreciate you help very much...

--ZG--
 
Back
Top