# 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.  Register To Reply

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  Register To Reply

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.  Register To Reply

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.  Register To Reply

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.  Register To Reply

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.  Register To Reply

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.  Register To Reply

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)  Register To Reply

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 ?  Register To Reply

10. ## Re: How to create a formula that keep tracks of new value. Originally Posted by fadu4u 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  Register To Reply

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.  Register To Reply

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

It works in Excel.  Register To Reply

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.  Register To Reply

14. ## Re: How to create a formula that keep tracks of new value. Originally Posted by fadu4u 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.  Register To Reply

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.  Register To Reply

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)   Register To Reply

17. ## Re: How to create a formula that keep tracks of new value. Originally Posted by fadu4u 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.   Register To Reply