Finding the last row

[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

 

[Domenic]

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!

 

[Craig Schiller]

Domenic -

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

Craig

 

[Domenic]

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!

 

[Craig Schiller]

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

 

[L. Howard Kittle]

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