2 steps question in microsoft excel sum 1 value with fluctuating values.
Hello everyone
This is a 2 step question in microsoft excel:
I would like to be able to take a cell with number 50 hrs which will go down anywhere to 0 while another cell will increase the difference between that 50 and 0.
Part 1:
Example: cell A1=50 cell A2=0 cell A3=0
now cell A1=45 cell A2=5 therefore still = 50 cell A3=5
Cell A1=32 cell A2=18 therefore still =50 cell A3=18
Cell A1=0 cell A2=50 cell A3=50
Now the next thing I need is another cell that would keep adding this A2 hrs that would keep accumulating even though these 2 values in A1 and A2 will keep going up and down anywhere between 0 and 50.
Part 2:
So here goes my example:
Example: cell A1=50 cell A2=0 cell A3=0
now cell A1=45 cell A2=5 therefore still = 50 ...cell A3=5
Cell A1=32 cell A2=18 therefore still =50 ...cell A3=18
Cell A1=0 cell A2=50 ...cell A3=50
Cell A1=50 cell A2=0 ...cell A3=50
Cell A1=36 cell A2=14 ...cell A3=(50+14)=64 therefore Cell A3 keep accumulating all the time whenever cell A2 increases
Cell A1=15 cell A2=35 ...cell A3 =(64+35)=99
Cell A1=7 cell A2=43 ...cell A3=(99+43)=142
This will keep going on and on and on.....therefore cell A3 will keep increasing.
Is there a way to do this formula work?
Thanks very much for any help with this.
Cell A1=50 cell A2=0 ...cell A3=98
This is a 2 step question in microsoft excel:
I would like to be able to take a cell with number 50 hrs which will go down anywhere to 0 while another cell will increase the difference between that 50 and 0.
Part 1:
Example: cell A1=50 cell A2=0 cell A3=0
now cell A1=45 cell A2=5 therefore still = 50 cell A3=5
Cell A1=32 cell A2=18 therefore still =50 cell A3=18
Cell A1=0 cell A2=50 cell A3=50
Now the next thing I need is another cell that would keep adding this A2 hrs that would keep accumulating even though these 2 values in A1 and A2 will keep going up and down anywhere between 0 and 50.
Part 2:
So here goes my example:
Example: cell A1=50 cell A2=0 cell A3=0
now cell A1=45 cell A2=5 therefore still = 50 ...cell A3=5
Cell A1=32 cell A2=18 therefore still =50 ...cell A3=18
Cell A1=0 cell A2=50 ...cell A3=50
Cell A1=50 cell A2=0 ...cell A3=50
Cell A1=36 cell A2=14 ...cell A3=(50+14)=64 therefore Cell A3 keep accumulating all the time whenever cell A2 increases
Cell A1=15 cell A2=35 ...cell A3 =(64+35)=99
Cell A1=7 cell A2=43 ...cell A3=(99+43)=142
This will keep going on and on and on.....therefore cell A3 will keep increasing.
Is there a way to do this formula work?
Thanks very much for any help with this.
Cell A1=50 cell A2=0 ...cell A3=98
ASKER
Hello GasperK
I will try this out today if time permits and see if I can make it work. Yes I will only run it once A2 is 50 that's fine. The reason for this is every time the impacts on our Jumbo drills get to 50 we have to do a maintenance PM on it as for the A3 cell total this will give me the exact hours/year/ impact on each percussion drill done.
Thanks very much...I will try it and post back my results.
Have a great day also...Merry Xmas and best wishes.
I will try this out today if time permits and see if I can make it work. Yes I will only run it once A2 is 50 that's fine. The reason for this is every time the impacts on our Jumbo drills get to 50 we have to do a maintenance PM on it as for the A3 cell total this will give me the exact hours/year/ impact on each percussion drill done.
Thanks very much...I will try it and post back my results.
Have a great day also...Merry Xmas and best wishes.
As above, but use this code:
Now every time you change the value in A1, C1 will automatically recalculate.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [A1]) Is Nothing Then
[C1] = [C1] + [B1]
End If
End SubNow every time you change the value in A1, C1 will automatically recalculate.
ASKER
Hello jell
Any chance you can elaborate a little on how to create this macro? I keep getting errors when i run it so not sure if i have it configured correctly.
Thanks for any help...greatly appreciated.
Any chance you can elaborate a little on how to create this macro? I keep getting errors when i run it so not sure if i have it configured correctly.
Thanks for any help...greatly appreciated.
ASKER
Hello again
I tried creating the macro but no luck. I'm not sure if I've got it configured properly. I'm not sure how to configure the macro.
Do I use Alt + f11 and add a module or do I record a macro? Can someone help me out with the steps.
Thanks very much for the help. I attached a photo of what I have tried.
impactmacro-1.jpeg
I tried creating the macro but no luck. I'm not sure if I've got it configured properly. I'm not sure how to configure the macro.
Do I use Alt + f11 and add a module or do I record a macro? Can someone help me out with the steps.
Thanks very much for the help. I attached a photo of what I have tried.
impactmacro-1.jpeg
Hi,
I only saw jell's macro. If you try with mine from "above" it will work. It's a little longer but requires no additional configuring...
Have a nice day
I only saw jell's macro. If you try with mine from "above" it will work. It's a little longer but requires no additional configuring...
Have a nice day
Hello,
May be you should try the code attached which changes the values in A2 and A3 for any change in A1.
Hope this is what you require.
Tils. impact-worksheet.xlsm
May be you should try the code attached which changes the values in A2 and A3 for any change in A1.
Hope this is what you require.
Tils. impact-worksheet.xlsm
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If k >= 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = j
Range("A2") = j
Range("A3") = k
End If
Else
Exit Sub
End If
End SubASKER
Hello everyone and thanks very much for the help.
The reason it took me a while to get back was that I'm a little green when it comes to programing this and had to figure out how to do this.
Tilsant:
This code works very well for the first 50 but once it goes back to 50 then the sum doesn't work anymore.
Ex: A1=50 A2=0 A3=0
A1=40 A2=10 A2=10
A1=35 A2=15 A3=15
A1= 0 A2=50 A3=50
A1=50 A2=0 A3=50
A1=40 A2=10 A3=60
A1=35 A2=15 A3=75 This is where the code stops functionning cause it adds A2 with A3 but it should only add 40-35=5 therefore A3=65 and not 75. Other then that this code would work perfectly.
Gasperk:
Same thing here. The code works well for the first time you enter a number but as soon as you enter a number the 2nd time then it adds the entire value of the cell A2 and not just the change of the cell.
A1=50 A2=0 A3=0
A1=45 A2=5 run macro A3=5
A1=35 A2=15 run macro but now A3=20 it started adding up the A2 and A3 values immediately therefore the answer is now 20 instead of 15.
I will try the one from jell and see if I can figure out how to create the macro itself and test it with results.
Thanks very much for the help. Please advise if someone can figure out the issue that I have. They are very close to what I need but not correct.
The reason it took me a while to get back was that I'm a little green when it comes to programing this and had to figure out how to do this.
Tilsant:
This code works very well for the first 50 but once it goes back to 50 then the sum doesn't work anymore.
Ex: A1=50 A2=0 A3=0
A1=40 A2=10 A2=10
A1=35 A2=15 A3=15
A1= 0 A2=50 A3=50
A1=50 A2=0 A3=50
A1=40 A2=10 A3=60
A1=35 A2=15 A3=75 This is where the code stops functionning cause it adds A2 with A3 but it should only add 40-35=5 therefore A3=65 and not 75. Other then that this code would work perfectly.
Gasperk:
Same thing here. The code works well for the first time you enter a number but as soon as you enter a number the 2nd time then it adds the entire value of the cell A2 and not just the change of the cell.
A1=50 A2=0 A3=0
A1=45 A2=5 run macro A3=5
A1=35 A2=15 run macro but now A3=20 it started adding up the A2 and A3 values immediately therefore the answer is now 20 instead of 15.
I will try the one from jell and see if I can figure out how to create the macro itself and test it with results.
Thanks very much for the help. Please advise if someone can figure out the issue that I have. They are very close to what I need but not correct.
ASKER
Hello jell
I figured out how to get this going but still not what I'm looking for. This is how this code works out:
A1=50 B1=0 C1=0
A1=40 B1=10 C1=10
A1=35 B1=15 but now C1=25 when it should be 15.
Thanks again to everyone for the help and sorry it took a bit of time but I had to figure out how to make these work since I'm pretty green at coding. If anyone can figure something out that would do what I need please advise. I will try keep working with these and see If I can't figure out some way to make one of these codes work for me.
I figured out how to get this going but still not what I'm looking for. This is how this code works out:
A1=50 B1=0 C1=0
A1=40 B1=10 C1=10
A1=35 B1=15 but now C1=25 when it should be 15.
Thanks again to everyone for the help and sorry it took a bit of time but I had to figure out how to make these work since I'm pretty green at coding. If anyone can figure something out that would do what I need please advise. I will try keep working with these and see If I can't figure out some way to make one of these codes work for me.
Here's the revised version.
Tils.
Tils.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If k >= 50 Then
If i = 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = Range("A3") + 50 - Range("A2") - i
Range("A2") = j
Range("A3") = k
End If
Else
k = j
Range("A2") = j
Range("A3") = k
End If
Else
Exit Sub
End If
End Sub
Your 'Part 2' of the question, did convey that A2 was to be added to A3 after the value crossing 50, and not adding the difference between new A1 and old A1.
Anyway, hope the previous solution works for you.
-Tils.
Anyway, hope the previous solution works for you.
-Tils.
Part 2:
So here goes my example:
Example: cell A1=50 cell A2=0 cell A3=0
now cell A1=45 cell A2=5 therefore still = 50 ...cell A3=5
Cell A1=32 cell A2=18 therefore still =50 ...cell A3=18
Cell A1=0 cell A2=50 ...cell A3=50
Cell A1=50 cell A2=0 ...cell A3=50
Cell A1=36 cell A2=14 ...cell A3=(50+14)=64 therefore Cell A3 keep accumulating all the time whenever cell A2 increases
Cell A1=15 cell A2=35 ...cell A3 =(64+35)=99
Cell A1=7 cell A2=43 ...cell A3=(99+43)=142
This will keep going on and on and on.....therefore cell A3 will keep increasingASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Tilsant
You are totally right and I did say that , my mistake. The code you now have works perfectly. If I may I would now require a little clarification for me to complete this in my situation:
I have to do this in the same worksheet for mutiple rows. The code you have created works perfectly for 1 machine. Now this must work for 6 different machines using the same pattern you could say in the same worksheet. Can I have multiple If, Else, End statements or do I include the other machines in this code?
Example:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If i = 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = Range("A3") + 50 - Range("A2") - i
Range("A2") = j
Range("A3") = k
End If
Else
Exit Sub
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
l = Range ("A21")
m = 50 - l
n = Range("A23")
If l = 50 Then
k = Range("A23") + m
Range("A22") = m
Range("A23") = n
Else
n = Range("A23") + 50 - Range("A22") - l
Range("A22") = l
Range("A23") = n
End If
Else
Exit Sub
End If
End Sub
Then I would keep going for 5 more machines. Is this the way I can achieve this?
Or is this not possible to code?
Thanks very much for the help.
You are totally right and I did say that , my mistake. The code you now have works perfectly. If I may I would now require a little clarification for me to complete this in my situation:
I have to do this in the same worksheet for mutiple rows. The code you have created works perfectly for 1 machine. Now this must work for 6 different machines using the same pattern you could say in the same worksheet. Can I have multiple If, Else, End statements or do I include the other machines in this code?
Example:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If i = 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = Range("A3") + 50 - Range("A2") - i
Range("A2") = j
Range("A3") = k
End If
Else
Exit Sub
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
l = Range ("A21")
m = 50 - l
n = Range("A23")
If l = 50 Then
k = Range("A23") + m
Range("A22") = m
Range("A23") = n
Else
n = Range("A23") + 50 - Range("A22") - l
Range("A22") = l
Range("A23") = n
End If
Else
Exit Sub
End If
End Sub
Then I would keep going for 5 more machines. Is this the way I can achieve this?
Or is this not possible to code?
Thanks very much for the help.
ASKER
Ok Here's what I did and I believe this is working exaclty the way it needs to. I only entered 1 more machine if you want and the code seems to work. I will do this for all 6 machines and see then post back.
Here's the code I now have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If i = 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = Range("A3") + 50 - Range("A2") - i
Range("A2") = j
Range("A3") = k
End If
End If
Dim l, m, n As Long
If Not Intersect(Target, ActiveSheet.Range("A21")) Is Nothing Then
l = Range("A21")
m = 50 - l
n = Range("A23")
If l = 50 Then
n = Range("A23") + m
Range("A22") = m
Range("A23") = n
Else
n = Range("A23") + 50 - Range("A22") - l
Range("A22") = m
Range("A23") = n
End If
End If
Exit Sub
End Sub
Here's the code I now have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
i = Range("A1")
j = 50 - i
k = Range("A3")
If i = 50 Then
k = Range("A3") + j
Range("A2") = j
Range("A3") = k
Else
k = Range("A3") + 50 - Range("A2") - i
Range("A2") = j
Range("A3") = k
End If
End If
Dim l, m, n As Long
If Not Intersect(Target, ActiveSheet.Range("A21")) Is Nothing Then
l = Range("A21")
m = 50 - l
n = Range("A23")
If l = 50 Then
n = Range("A23") + m
Range("A22") = m
Range("A23") = n
Else
n = Range("A23") + 50 - Range("A22") - l
Range("A22") = m
Range("A23") = n
End If
End If
Exit Sub
End Sub
ASKER
Ok
i tried it in 4 machines (still in the example not the real document yet) and all seems to work perfectly for all of them.
I want to thank everyone for all their help and efforts. I do apologize for the error made that Tilsant pinpointed out and amount of time it took for me to reply back but I had to learn how to make this work (newbie programmer) with the codes created.
Again thanks very much to all it is greatly appreciated.
i tried it in 4 machines (still in the example not the real document yet) and all seems to work perfectly for all of them.
I want to thank everyone for all their help and efforts. I do apologize for the error made that Tilsant pinpointed out and amount of time it took for me to reply back but I had to learn how to make this work (newbie programmer) with the codes created.
Again thanks very much to all it is greatly appreciated.
ASKER
Thanks very very much for the help.
Hello,
Can't you just copy the same file in all of the 6 machines.
No need for more variables nor more loops.
Or simply create a new macro enabled workbook and paste the code in the required sheet module.
Am I missing anything?
Tils.
Can't you just copy the same file in all of the 6 machines.
No need for more variables nor more loops.
Or simply create a new macro enabled workbook and paste the code in the required sheet module.
Am I missing anything?
Tils.
ASKER
Hello,
No I'm tracking the engine and drill impact hours on around 114 machines and keep growing all the time they are all in the same worksheet. What I'm doing is tracking the hour meters on engines and drill impacts hours which need to have preventive maintenance services done on them at intervals and I have to keep track of hours also for budgeting purposes at the end of the year. The way you did that code works very well in the test worksheet I've done. Tomorrow I'll implement it in the actual copy of the document and check it but all should be fine.
I do have to add another type of code in here for another calculation that must be done in the same document but I'll open another post so you can get points for it.
Basically this one is:
Cell A44=0 Cell A45=0
Cell A44=20 Cell A45=20
Cell A44=50 Cell A45=50
Cell A44=3000 Cell A45=3000
This keeps going on until I have to change an hour meter or an engine on a machine. Therefore now
Cell A44=0 Cell A45=3000
Cell A44=24 Cell A45=3024
Cell A44=157 Cell A45= 3024+157=3181
In other words cell A44 can be anything from 0 to whatever but will 0 out eventually. It will zero out more then once through the years. Cell A45 should just keep adding up the hours (for 1 year then I'll zero both of them and start again.
There are actually about 114 pieces of equipment counting plated vehicles. (So A1 to A114 let's say)
NOTE: This must be implemented for any amount of machines and I must be able to add on as we purchase more.
Like I said I'll start a new thread for this one for point purposes.
Thanks very much for the help. I really appreciate you and this site for all the support and help I 've always received here.
My hat's off to all of you here.
No I'm tracking the engine and drill impact hours on around 114 machines and keep growing all the time they are all in the same worksheet. What I'm doing is tracking the hour meters on engines and drill impacts hours which need to have preventive maintenance services done on them at intervals and I have to keep track of hours also for budgeting purposes at the end of the year. The way you did that code works very well in the test worksheet I've done. Tomorrow I'll implement it in the actual copy of the document and check it but all should be fine.
I do have to add another type of code in here for another calculation that must be done in the same document but I'll open another post so you can get points for it.
Basically this one is:
Cell A44=0 Cell A45=0
Cell A44=20 Cell A45=20
Cell A44=50 Cell A45=50
Cell A44=3000 Cell A45=3000
This keeps going on until I have to change an hour meter or an engine on a machine. Therefore now
Cell A44=0 Cell A45=3000
Cell A44=24 Cell A45=3024
Cell A44=157 Cell A45= 3024+157=3181
In other words cell A44 can be anything from 0 to whatever but will 0 out eventually. It will zero out more then once through the years. Cell A45 should just keep adding up the hours (for 1 year then I'll zero both of them and start again.
There are actually about 114 pieces of equipment counting plated vehicles. (So A1 to A114 let's say)
NOTE: This must be implemented for any amount of machines and I must be able to add on as we purchase more.
Like I said I'll start a new thread for this one for point purposes.
Thanks very much for the help. I really appreciate you and this site for all the support and help I 've always received here.
My hat's off to all of you here.
Oh.. i misunderstood the word 'machine', i thought it meant 'computer' instead of 'drill/impact' machine.
In the attached file, changes in cell A1, A21, A41 would do the required changes.
More cell references (to add more machines) can be added in the 5th line of the code as required.
Hope this helps.
Tils.
In the attached file, changes in cell A1, A21, A41 would do the required changes.
More cell references (to add more machines) can be added in the 5th line of the code as required.
Hope this helps.
Tils.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j, k As Long
If Not Intersect(Target, ActiveSheet.Range("A1, A21, A41")) Is Nothing Then
i = Target
j = 50 - i
k = Target.Offset(2, 0)
If i = 50 Then
k = Target.Offset(2, 0) + j
Else
k = k + 50 - Target.Offset(1, 0) - i
End If
Target.Offset(1, 0) = j
Target.Offset(2, 0) = k
Else
Exit Sub
End If
End Sub
here is my idea,
You leave cell A1 as is, and manually change that value.
In cell A2 you write =50-A1
For cell A3 you have a problem, because you will do a circular reference since the formula you want is =A3+A2. So my advice is to resolve this by a simle macro:
Sub AddAValue()
Dim s1 As Integer, s2 As Integer, s3 As Integer
s1 = Range("A3").Value
s2 = Range("A2").Value
s3= s1 + s2
Range("A3").Value = s3
End Sub
You run this every time the value in A2 changes.
Not quite shure if you want to start adding only when the value in A2 is 50. Could not get that from the example....
Have a nice day....