Finding the last row

  • Thread starter Thread starter Craig Schiller
  • Start date Start date
C

Craig Schiller

Hi gurus -

I've posted this before but none of the responses I received worked
properly, so maybe I didn't state my question clearly. Here goes again:

In my spreadsheet I need to add two cells: one is in the last row of
column C and one is 3 rows above it. So, essentially, I'm looking to add
C{last row} + C{last row -3}. I can't figure out what function to use
that will automatically figure out what the number of the last row is.
Can anyone shed some light?

TIA,
Craig
 
First, define the following...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try the following formula...

=SUM(INDEX(C2:C65536,MATCH(BigNum,C2:C65536)):INDEX(C2:C65536,MATCH(BigNu
m,C2:C65536)-3))

Hope this helps!
 
Domenic -

Thanks for the thought, but this formula too produces an incorrect result.

Craig
 
Sorry Craig, my mistake! Try the following instead...

=SUM(INDEX(C2:C65536,MATCH(BigNum,C2:C65536)),INDEX(C2:C65536,MATCH(BigNu
m,C2:C65536)-3))

Hope this helps!
 
Thanks Domenic, that works!

Although I have no idea why. LOL. I don't get the logic of the formula
at all. But thank you very much!

Craig
 
Hi Craig,

Maybe this will help you understand the formula that Domenic offered.

INDEX(A:A,MATCH(9.99999999999999E+307,A:A),1)

Returns last value in column A because 9.99999999999999E+307 is a huge
number which will most likely will never occur in your column so the formula
returns the last VALUE in the column A by default. (If A10 = 25 and A11,
A12, A13 had text in them, you get 25.)

If you knew for certain the largest number that would EVER be in column A
was 100, you could use 101 instead of the 9.99999999999999E+307 and it would
work the same.

So what Domenic did was to make a name that refers to 9.99999999999999E+307
and use that name in the formula to shorten it.

This returns the value 3 rows up from the last value, notice the -3 near the
end.

INDEX(A:A,MATCH(9.99999999999999E+307,A:A)-3,1)

So if you wanted to really shorten the formula you could define > insert >
name and refer to the entire formulas instead of just 9.999...'s. Then, in
my case here, the formula you would use on your sheet would look like this.

=SUM(Lastnum,Thirdnum)

HTH
Regards,
Howard
 
Back
Top