Results 1 to 3 of 3

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

  1. #1

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

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    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

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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

    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
        Application.EnableEvents = True
    End Sub

  3. #3
    thank you bob, it works 100%!
    yours in Christ Jesus,

    ps, how do i mark this as solved?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts