+ Reply to Thread
Results 1 to 17 of 17

How to create a formula that keep tracks of new value.

  1. #1
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    How to create a formula that keep tracks of new value.

    A B C
    1 10 0 =B4
    2 10 10
    3 10 20
    4 10 30
    5 inputting new values -- > C1 changes it's value from =B4 to B5 as new value gets created.

    How do I create an excel formula that changes itself (C1) to =B4 -- > B5 -- > B6 etc, whenever a new cell is created at B5 and 6 and so on.
    Last edited by fadu4u; 07-31-2014 at 09:05 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,330

    Re: How to create a formula that adds or subtracts, whenever a new cell value is created.

    Untested, but try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that adds or subtracts, whenever a new cell value is created.

    Almost worked but this is strange, It gave me value on cell F15 however, the cells are filled until F18.

    I used this: =INDEX(F:F , COUNT(F:F))



    EDITED: I just used =COUNT(F:F) it gave me result as there are 15 rows same F15, despite till F18 cells are completely full.

    it fails to recognize values over F15 for some reason.
    Last edited by fadu4u; 07-31-2014 at 07:59 PM. Reason: New information added.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,330

    Re: How to create a formula that adds or subtracts, whenever a new cell value is created.

    Suggest you post a sample workbook so we can see what you see.

  5. #5
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that adds or subtracts, whenever a new cell value is created.

    It is working on sample workbook, except my actual workbook.

  6. #6
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that adds or subtracts, whenever a new cell value is created.

    I copied my actual data to a sample workbook for you.
    Same problem occuring.



    https://docs.google.com/spreadsheets...it?usp=sharing

  7. #7
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that keep tracks of new value.

    [FIXED] I had to define First data in the range, simply F:F won't work F5:F will work where F5 is the head of the data.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to create a formula that keep tracks of new value.

    If you want the last (bottom-most) numeric value in the column:

    =LOOKUP(1E100,F:F)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that keep tracks of new value.

    this may also work, but what is 1E100 in this formula?

    can I also use multiplication in my lookup formula? =LOOKUP(1E100,F:F) * B10 ?

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to create a formula that keep tracks of new value.

    Quote Originally Posted by fadu4u View Post
    this may also work, but what is 1E100 in this formula?
    1E100 is scientific notation (a shorthand method for expressing very long numbers) for the very large number 1 followed by 100 zeros.

    How it works...

    If the lookup value 1E100 is larger than any number in the range the formula will return the last (bottom-most) number in the range. There is a 100% chance that no numbers you're working with are anywhere close to being 1E100.

    can I also use multiplication in my lookup formula? =LOOKUP(1E100,F:F) * B10 ?
    Yes

  11. #11
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that keep tracks of new value.

    it doesn't work.

    check here, I used your formula on this sample sheet.

    https://docs.google.com/spreadsheets...it?usp=sharing

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to create a formula that keep tracks of new value.

    I don't know anything about Google docs.

    It works in Excel.

  13. #13
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that keep tracks of new value.

    it doesn't even work on excel.

    I used this =LOOKUP(1E+100,D:D )

    and the data is in row D. Nevermind I got my job done with the other formula, thanks for help.

  14. #14
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to create a formula that keep tracks of new value.

    Quote Originally Posted by fadu4u View Post
    it doesn't even work on excel.
    Sure it does.

    I used this =LOOKUP(1E+100,D:D )

    and the data is in row D.
    Do you mean column D?

    Here's a sample file.

    The formula will return the last (bottom-most) numeric value from column D.

    Start deleting the cells from the bottom of column D and notice the formula results.
    Attached Files Attached Files
    Last edited by Tony Valko; 07-31-2014 at 09:40 PM.

  15. #15
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that keep tracks of new value.

    lol it didn't work when the first time, now it does.
    Thanks, I'll add reputation.

  16. #16
    Registered User
    Join Date
    12-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to create a formula that keep tracks of new value.

    further more it also works on google docs now.

    Reason, your mistake :P its =Lookup(1E+100, D:D) instead you wrote =lookup(1E100, D:D)

  17. #17
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to create a formula that keep tracks of new value.

    Quote Originally Posted by fadu4u View Post
    further more it also works on google docs now.

    Reason, your mistake :P its =Lookup(1E+100, D:D) instead you wrote =lookup(1E100, D:D)
    Excel automatically adds the plus sign.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Button that subtracts .76, then subtracts from abother cell
    By THEQ100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2014, 09:41 AM
  2. [SOLVED] Need a formula that subtracts or adds a a specific value
    By superchew in forum Excel General
    Replies: 2
    Last Post: 10-22-2013, 12:23 PM
  3. Need to create a formula that either subtracts or displays 0
    By FlowrHuntr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2013, 04:02 PM
  4. [SOLVED] Sum adds rather than subtracts negative numbers?
    By innatedoc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-23-2012, 01:31 AM
  5. creating a button that adds/subtracts
    By gbWildy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-26-2007, 01:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1