Results 1 to 9 of 9
  1. #1
    Hall-Of-Famer
    Points: 65,231, Level: 79
    Level completed: 24%, Points required for next Level: 1,219
    Overall activity: 1.0%
    Achievements:
    SocialVeteran50000 Experience Points
    Cyclonesrule91's Avatar
    Join Date
    Apr 2006
    Location
    Waukee
    Posts
    4,949
    Points
    65,231
    Level
    79
    Thumbs Up
    Received: 15
    Given: 117

    Another Excel question

    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.



  2. #2
    Addict
    Points: 130,914, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 8.0%
    Achievements:
    Veteran50000 Experience Points
    theshadow's Avatar
    Join Date
    Apr 2006
    Posts
    6,675
    Points
    130,914
    Level
    100
    Thumbs Up
    Received: 99
    Given: 0

    Re: Another Excel question

    The easy way for part B is...

    A6 = 10
    A7 = 12

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



  3. #3
    Hall-Of-Famer
    Points: 65,231, Level: 79
    Level completed: 24%, Points required for next Level: 1,219
    Overall activity: 1.0%
    Achievements:
    SocialVeteran50000 Experience Points
    Cyclonesrule91's Avatar
    Join Date
    Apr 2006
    Location
    Waukee
    Posts
    4,949
    Points
    65,231
    Level
    79
    Thumbs Up
    Received: 15
    Given: 117

    Re: Another Excel question

    Quote Originally Posted by theshadow View Post
    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.



  4. #4
    Walk On
    Points: 6,886, Level: 24
    Level completed: 68%, Points required for next Level: 164
    Overall activity: 1.0%
    Achievements:
    Veteran5000 Experience Points
    Nothingman's Avatar
    Join Date
    Mar 2006
    Posts
    129
    Points
    6,886
    Level
    24
    Thumbs Up
    Received: 4
    Given: 15

    Re: Another Excel question

    Quote Originally Posted by Cyclonesrule91 View Post
    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.



  5. #5
    Addict
    Points: 150,878, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 23.0%
    Achievements:
    SocialVeteran50000 Experience PointsRecommendation Second Class
    Farnsworth's Avatar
    Join Date
    Apr 2006
    Location
    Omaha, NE
    Posts
    8,021
    Points
    150,878
    Level
    100
    Thumbs Up
    Received: 682
    Given: 358

    Re: Another Excel question

    Quote Originally Posted by Nothingman View Post
    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).



  6. #6
    Walk On
    Points: 6,886, Level: 24
    Level completed: 68%, Points required for next Level: 164
    Overall activity: 1.0%
    Achievements:
    Veteran5000 Experience Points
    Nothingman's Avatar
    Join Date
    Mar 2006
    Posts
    129
    Points
    6,886
    Level
    24
    Thumbs Up
    Received: 4
    Given: 15

    Re: Another Excel question

    Quote Originally Posted by Farnsworth View Post
    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)))



  7. #7
    Addict
    Points: 150,878, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 23.0%
    Achievements:
    SocialVeteran50000 Experience PointsRecommendation Second Class
    Farnsworth's Avatar
    Join Date
    Apr 2006
    Location
    Omaha, NE
    Posts
    8,021
    Points
    150,878
    Level
    100
    Thumbs Up
    Received: 682
    Given: 358

    Re: Another Excel question

    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.



  8. #8
    Hall-Of-Famer
    Points: 65,231, Level: 79
    Level completed: 24%, Points required for next Level: 1,219
    Overall activity: 1.0%
    Achievements:
    SocialVeteran50000 Experience Points
    Cyclonesrule91's Avatar
    Join Date
    Apr 2006
    Location
    Waukee
    Posts
    4,949
    Points
    65,231
    Level
    79
    Thumbs Up
    Received: 15
    Given: 117

    Re: Another Excel question

    Quote Originally Posted by Nothingman View Post
    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.
    Quote Originally Posted by Farnsworth View Post
    fixed. (add -1, bolded above).
    Guys, That is EXACTLY what I was looking for!!!!

    Much thanks go to the both of you!!!



  9. #9
    Hall-Of-Famer
    Points: 65,231, Level: 79
    Level completed: 24%, Points required for next Level: 1,219
    Overall activity: 1.0%
    Achievements:
    SocialVeteran50000 Experience Points
    Cyclonesrule91's Avatar
    Join Date
    Apr 2006
    Location
    Waukee
    Posts
    4,949
    Points
    65,231
    Level
    79
    Thumbs Up
    Received: 15
    Given: 117

    Re: Another Excel question

    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
  • TV: TBD
  • Iowa State vs. Oklahoma
  • November 1, 2014
  • 07:00 PM