Another Excel question

[Cyclonesrule91]

Hoping some Excel experts out there can help me figure out how to do something in excel.

If I have a list of numbers all in row A for example that everyday I enter another number at the bottom of the list, is there a way I can use a formula to:

1. Give me the last number on the list?
2. Tell me the difference between that last number and the one prior to it?

So if I have 10 in cell A6 and today I add 12 in cell A7, I would want a selected cell to tell me 12 was the last number and another cell to tell me +2.

Can anybody help me with this? Thank you in advance.

 

[theshadow]

The easy way for part B is...

A6 = 10
A7 = 12

In B7, type "=A7-A6" ... then fill down as far as necessary.

 

[Cyclonesrule91]

The easy way for part B is...

A6 = 10
A7 = 12

In B7, type "=A7-A6" ... then fill down as far as necessary.

I would use that in most instances, but I need this data to be always showing up in one cell. So If I could find a "Last number" type of equation then I could use that for my last entry as well as the difference.

 

[Nothingman]

I would use that in most instances, but I need this data to be always showing up in one cell. So If I could find a "Last number" type of equation then I could use that for my last entry as well as the difference.

You can use this code to get the last number.

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

and this code to get the second to last number.

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

Then just subtract the two.

 

[Farnsworth]

You can use this code to get the last number.

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

and this code to get the second to last number.

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

Then just subtract the two.

fixed. (add -1, bolded above).

 

[Nothingman]

fixed. (add -1, bolded above).

Thanks, looks like I just copied and pasted the same formula twice. I used this formula to get the second to last number.

=LOOKUP(9.9E+307,INDIRECT("A1:A"&MATCH(LOOKUP(9.9E+307,A:A)-1,A:A)))

 

[Farnsworth]

If you don't want to waste cells to get the two numbers, then remap it to your plus minus field, just combine the two.

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))-LOOKUP(9.9E+307,INDIRECT("A1:A"&MATCH(LOOKUP(9.9E+307,A:A)-1,A:A)))

I don't know how to format a "+" in front of it for a positive outcome, I would just do conditional formatting making it green or red.

 

[Cyclonesrule91]

You can use this code to get the last number.

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

and this code to get the second to last number.

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

Then just subtract the two.

fixed. (add -1, bolded above).

Guys, That is EXACTLY what I was looking for!!!!

Much thanks go to the both of you!!!

 

[Cyclonesrule91]

OK, next question on this project I am working on. I got the search last function going thanks to Farnsworth and Nothingman and am very grateful for that. Let me just get that out there first and foremost.

Next question I have is...is there a way that the data that goes to the bottom of the list after daily closes of the markets can be automatically calculated and entered at the bottom of the list after I enter the close information on another page? I have some calculated data that will change occasionally and I have numbers that would change daily that would get me data that I then paste at the bottom of the list or drag the equation down another line presently.