Calculator: Given 3 variables, find 4th

philro

New Member
Joined
Dec 20, 2009
Messages
14
I would like to create a userform calculator that will calculate the missing fourth value when any three other values are given.

The formula is similar to y = a * b * c

At any given time I know three of the values but need a fourth quickly. I'm new to VBA and presume I need a separate formula for each variable. Long If.. Then statements???

Advice on keywords to search for or locations to find similar coding would be appreciated.

Thx,
philro
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
I would like to create a userform calculator that will calculate the missing fourth value when any three other values are given.

The formula is similar to y = a * b * c

At any given time I know three of the values but need a fourth quickly. I'm new to VBA and presume I need a separate formula for each variable. Long If.. Then statements???

Advice on keywords to search for or locations to find similar coding would be appreciated.

Thx,
philro

Hi and welcome to the board!!

It is possible to make this, but you would need to know the equations that establish relation between the variables.

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 

philro

New Member
Joined
Dec 20, 2009
Messages
14
I know the equation and it's similar to what's given here.

In cell 1: x=a*b*c
cell 2: a=x/(b*c)
cell 3: b=x/(a*c)
cell 4: c=x/(a*b)

How do I set this up in a calculator format such that the user enters three of the values and the fourth value is returned when the calculate button is hit? I want to make this reusable so that the user can not clear out the values but tweak the given variable to get the forth without having to reenter all values.

Thx
 

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
Your basic code should be something like

Code:
    If a = 0 Then
        a = x / (b * c)
    ElseIf b = 0 Then
        b = x / (a * c)
    ElseIf c = 0 Then
        c = x / (a * b)
    ElseIf x = 0 Then
        x = a * b * c
    End If

How you utilize this code will depend on how your userform is designed.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 

philro

New Member
Joined
Dec 20, 2009
Messages
14
0 and blank/empty are not the same. How does one test for the "empty/blank" cell?

The code you suggested would work for a single cell but how would this work in each of the four cells? Would the computed "a" value show in the corresponding cell? Would the computed "b" value show in its cell?

I guess I'm envisioning a form with 4 empty "boxes" and a few buttons. Each box corresponds to one of the variables. How would a typical empty "box's" code read? If a value is placed in the 3 given boxes, would it be better to check for <> 0 (blank) in those boxes and the remaining box is computed?

Thx
 

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
I have made a sample workbook.

PM me your email id and i'll send it to you.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 

Watch MrExcel Video

Forum statistics

Threads
1,127,600
Messages
5,625,741
Members
416,132
Latest member
Chandan Choubey

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
Top