Get previous value before change, subtract that from after change, and output to other cell?

xCensored

New Member
Joined
Dec 20, 2014
Messages
8
Hey guys. Hopefully you guys can understand what I mean and can help me out!

A1 = time start using (cntrl+shift+;) which shows 4:00 AM
B1 = time end using (cntrl+shift+;) which shows 8:00 AM
C1 = time between using =MOD(B1-A1,1) which shows 4:00:00
D1 = time from C3 converted into minutes using =C1*1440 which shows 240 minutes
E1 = D1 new value - D1 old value (Example: if old value was 240, and new is 360, then 360-240 = 120.)

I have A1, B1, C1, and D1, and not sure how to formulate E1. I came across a few results on google about returning old value, and I got results that had to do with VBA, which didn't go step by step how to set it up, just provide the code. If my problem does require a VBA code, I'd appreciate if you could also include step by step on how to set it up and run it! I would greatly appreciate it! Thanks in advance, and to those who have no idea what I am trying to accomplish, I am sorry, and I will do my best to explain it more in detail as you wish.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi and welcome to the MrExcel Message Board.

Basically, you need to save the "old" value prior to changing it.

I get where D1 comes from but how and when is it changed? How would Excel know when to save the value? Is it each time the worksheet changes or only when it is saved or after the user pushes a button or ...?
 
Upvote 0
Hi and welcome to the MrExcel Message Board.

Basically, you need to save the "old" value prior to changing it.

I get where D1 comes from but how and when is it changed? How would Excel know when to save the value? Is it each time the worksheet changes or only when it is saved or after the user pushes a button or ...?
Hi Rick, thanks for replying! The values I'll be changing are A1 and B1. C1, D1, and E1 will be changing according to the times I put into A1 and B1.
 
Upvote 0
Hi,

Thanks, but I think I had that bit worked out.

Let me try some scenarios.

1. When someone makes a change to A1, say, and E1 is updated, what do you want to happen if that person nthen makes a correction to A1. Do you want the original old value to be used for the comparison or the new one that was generated after the change?

2. If E1 has already been created and someone re-opens the spreadsheet and makes another change should the current old value be used or the one that was used to create the original E1?

For Excel to save an "old" value, something needs to know when that value needs to be saved and how often it needs updating. Should it be updated every time the worksheet is changed, every time it is saved or should it be that the first ever value should be retained?
 
Upvote 0
Hi,

Thanks, but I think I had that bit worked out.

Let me try some scenarios.

1. When someone makes a change to A1, say, and E1 is updated, what do you want to happen if that person nthen makes a correction to A1. Do you want the original old value to be used for the comparison or the new one that was generated after the change?

2. If E1 has already been created and someone re-opens the spreadsheet and makes another change should the current old value be used or the one that was used to create the original E1?

For Excel to save an "old" value, something needs to know when that value needs to be saved and how often it needs updating. Should it be updated every time the worksheet is changed, every time it is saved or should it be that the first ever value should be retained?
1. If I made a change in A1, I want E1 to make the change of the new one.
2. I want the current old value to be used if I make a new change to either A1 or B1.
3. I want E1 to update everytime there is a change to A1 and B1.

A1 = 4:00 AM
B1 = 8:00 AM
C1 = =MOD(B1-A1,1) = 4:00:00
D1 = =C1*1440 = 240
E1 = 240

then, later on, I update B1

A1 = 4:00 AM // time start
B1 = 9:00 AM // time finish
C1 = =MOD(B1-A1,1) = 5:00:00 // time between
D1 = =C1*1440 = 300 // converted to minutes
E1 = 60 // new value of D1 - old value of D1

I hope I answered your questions and the further information helps..
 
Upvote 0
Hi again.

I think this does what you have asked for.

First you need to read the current value of D1 when you start thew workbook.
Then you need to update it every time the worksheet changes.
Also you need to calculate E1.

To save the first value of D1 you need this as a workbook macro. You need to go into the Visual Basic Editor, double click the entry marked ThisWorkbook and paste it in there.
Code:
Private Sub Workbook_Open()
    D1 = Worksheets("Sheet3").Range("D1").Value
End Sub

Then you will need to create an ordinary Module using the Insert-->Module command.
Code:
Public D1 As Long
Function xDiff() As Long
    Application.Volatile
    xDiff = Range("D1").Value - D1
    D1 = xDiff
End Function
The first line creates a global macro value called D1.
The function is how you will calculate E1. So in cell E1 you will need
Code:
=xDiff()
The function will calculate the new value for E1 and also save the new value of D1 in the global variable.
 
Upvote 0
Hi again.

I think this does what you have asked for.

First you need to read the current value of D1 when you start thew workbook.
Then you need to update it every time the worksheet changes.
Also you need to calculate E1.

To save the first value of D1 you need this as a workbook macro. You need to go into the Visual Basic Editor, double click the entry marked ThisWorkbook and paste it in there.
Code:
Private Sub Workbook_Open()
    D1 = Worksheets("Sheet3").Range("D1").Value
End Sub

Then you will need to create an ordinary Module using the Insert-->Module command.
Code:
Public D1 As Long
Function xDiff() As Long
    Application.Volatile
    xDiff = Range("D1").Value - D1
    D1 = xDiff
End Function
The first line creates a global macro value called D1.
The function is how you will calculate E1. So in cell E1 you will need
Code:
=xDiff()
The function will calculate the new value for E1 and also save the new value of D1 in the global variable.
I've done what you said, added the necessary code, and it's almost there! E1 is outputting numbers four digit positive and negative numbers which it shouldn't, as difference in minutes between A1 and B1 should be only positive. If D1 is 5, and then updated to 7, both derived from A1, B1, and C1, then, E1 should spit out 2 but this isn't the case.
 
Upvote 0
Hi,

I woke up this morning thinking that I should have not used Application.Volatile because any change to the sheet will force D1 to be re-calculated.

Please try this revised version. Only the macro in the Module needs to be changed along with the formula in the worksheet:
Code:
Public D1 As Long
Function xDiff(r As Range)
    xDiff = r.Value - D1
    Debug.Print "Old:"; D1; "New:"; xDiff
    D1 = xDiff
End Function

Worksheet formula:
Code:
=xDiff(D1)
Now the formula will only recalculate when D1 changes.

Also, note the Debug.Print statement in the Function. This should be removed after the debuigging process is compl;ete. Hoiwever, it will print out the old and new versions of D1 every time it changes. This will be displayed in the Immediate window of the Visual Basic Editor.

It will show that D1 can go negative based on the rules provided. The rule is New - Old. If Old is bigger than New then the answer will be negative. Perhaps by looking at the Debug output you can work out what you actually need to happen.
 
Upvote 0
Hi,

I woke up this morning thinking that I should have not used Application.Volatile because any change to the sheet will force D1 to be re-calculated.

Please try this revised version. Only the macro in the Module needs to be changed along with the formula in the worksheet:
Code:
Public D1 As Long
Function xDiff(r As Range)
    xDiff = r.Value - D1
    Debug.Print "Old:"; D1; "New:"; xDiff
    D1 = xDiff
End Function

Worksheet formula:
Code:
=xDiff(D1)
Now the formula will only recalculate when D1 changes.

Also, note the Debug.Print statement in the Function. This should be removed after the debuigging process is compl;ete. Hoiwever, it will print out the old and new versions of D1 every time it changes. This will be displayed in the Immediate window of the Visual Basic Editor.

It will show that D1 can go negative based on the rules provided. The rule is New - Old. If Old is bigger than New then the answer will be negative. Perhaps by looking at the Debug output you can work out what you actually need to happen.
I actually took my problem to StackOverflow right when I submitted this thread, and got the answer from there instead. Thank you Rick for your help nonetheless!
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top