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

1. ## 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.

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

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

Regards, TMS

3. ## 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.

4. ## 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. ## 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. ## 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.

7. ## 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. ## 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)

9. ## 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. ## Re: How to create a formula that keep tracks of new value.

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. ## 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.

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

It works in Excel.

13. ## 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. ## Re: How to create a formula that keep tracks of new value.

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.

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

lol it didn't work when the first time, now it does.

16. ## 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. ## 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)
Excel automatically adds the plus sign.

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

#### 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