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
ibanez7Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gašper KamenšekExcel MVPCommented:
Hi,

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....
0
ibanez7Author Commented:
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.
0
James ElliottManaging DirectorCommented:
As above, but use this code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, [A1]) Is Nothing Then

    [C1] = [C1] + [B1]

End If

End Sub

Open in new window


Now every time you change the value in A1, C1 will automatically recalculate.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ibanez7Author Commented:
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.
0
ibanez7Author Commented:
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
0
Gašper KamenšekExcel MVPCommented:
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
0
tilsantCommented:
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
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 Sub

Open in new window

0
ibanez7Author Commented:
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.
0
ibanez7Author Commented:
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.
0
tilsantCommented:
Here's the revised version.


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

Open in new window

impact-worksheet.xlsm
0
tilsantCommented:
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.

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

Open in new window

0
tilsantCommented:
Here's a simplified code, giving the same result.
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

Open in new window

impact-worksheet-v2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ibanez7Author Commented:
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.
0
ibanez7Author Commented:
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

0
ibanez7Author Commented:
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.
0
ibanez7Author Commented:
Thanks very very much for the help.
0
tilsantCommented:
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. sheet module
0
ibanez7Author Commented:
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.
0
tilsantCommented:
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.
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

Open in new window

impact-worksheet-v3.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.