x,y,z=100% - if x = 77, what value y and z...

ggsmit

ggsmit

New member
Joined
Feb 20, 2014
Messages
2
Reaction score
0
Points
0
hi
math has never been my forte, never mind excel :-/
the problem:
i have to make a mixture of three substances that have to make 100% [per mass].
substance x = 77% of the mixture
substance y = 18% of the mixture
substance z = 5% of the mixture
total=100%
i want to have/make a macro or run a vba or set up a sheet where i can enter values for x y or z and get the rest of the calculation done for me.
any help will be more than welcome!
in Christ
gabriel smit
 
This code goes in the sheet code modulke and automatically updates whe A2, A3 or A4 is changed. It assumes A2 is x, A3 is y, and A4 is z

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    Select Case True
    
        Case Not Intersect(Target, Me.Range("A2")) Is Nothing
        
            Me.Range("A3").Value = Target.Value / 77 * 18
            Me.Range("A4").Value = Target.Value / 77 * 5
    
        Case Not Intersect(Target, Me.Range("A3")) Is Nothing
        
            Me.Range("A2").Value = Target.Value / 18 * 77
            Me.Range("A4").Value = Target.Value / 18 * 5
    
        Case Not Intersect(Target, Me.Range("A4")) Is Nothing
        
            Me.Range("A2").Value = Target.Value / 5 * 77
            Me.Range("A3").Value = Target.Value / 5 * 18
    End Select


ws_exit:
    Application.EnableEvents = True
End Sub
 
thank you bob, it works 100%!
yours in Christ Jesus,
gabriel

ps, how do i mark this as solved?
 
You must log in or register to reply here.
Back
Top