Recalculate based on 3 of 4 variables.
 

I have been asked to come up with a smple way to let non technical users key
in 3 values on a sheet and have the sheet calculate the 4th value. It is a
very easy calculation, but I'm not sure how to allow for any of the three
values to be entered and calculate the fourth. Here are the values

A1 Revenue (in dollars)
A2 Variable Costs (percentge)A
3 Fixed Costs (In dollars)
A4 Profit

The formula for A4 is =A1*(1-A2)-A3 (This gives the profit).

My problem is that my boss wants his employees to be able to change the
value of A4 (the profit) and let the system recalculate A1, A2 or A3
depending on which one they leave blank!

Any Ideas?

Recalculate based on 3 of 4 variables.
 

One way would be to give an answer in B1,B2,B3,B4 alongside whichever one is
left blank of A1,A2,A3,A4.

The formulae could be:
B1 =IF(AND(A1="",COUNT(A2:A4)=3),(A4+A3)/(1-A2),"")
B2 =IF(AND(A2="",COUNT(A1,A3,A4)=3),1-((A4+A3)/A1),"")
B3 =IF(AND(A3="",COUNT(A1,A2,A4)=3),A1*(1-A2)-A4,"")
B4 =IF(AND(A4="",COUNT(A1:A3)=3),A1*(1-A2)-A3,"")
--
David Biddulph

"pk" wrote in message
...
I have been asked to come up with a smple way to let non technical users
key
in 3 values on a sheet and have the sheet calculate the 4th value. It is
a
very easy calculation, but I'm not sure how to allow for any of the three
values to be entered and calculate the fourth. Here are the values

A1 Revenue (in dollars)
A2 Variable Costs (percentge)A
3 Fixed Costs (In dollars)
A4 Profit

The formula for A4 is =A1*(1-A2)-A3 (This gives the profit).

My problem is that my boss wants his employees to be able to change the
value of A4 (the profit) and let the system recalculate A1, A2 or A3
depending on which one they leave blank!

Any Ideas?

Recalculate based on 3 of 4 variables.
 

Thanks David, this gives me a great start.

Best Regarsd.

"David Biddulph" wrote:

One way would be to give an answer in B1,B2,B3,B4 alongside whichever one is
left blank of A1,A2,A3,A4.

The formulae could be:
B1 =IF(AND(A1="",COUNT(A2:A4)=3),(A4+A3)/(1-A2),"")
B2 =IF(AND(A2="",COUNT(A1,A3,A4)=3),1-((A4+A3)/A1),"")
B3 =IF(AND(A3="",COUNT(A1,A2,A4)=3),A1*(1-A2)-A4,"")
B4 =IF(AND(A4="",COUNT(A1:A3)=3),A1*(1-A2)-A3,"")
--
David Biddulph

"pk" wrote in message
...
I have been asked to come up with a smple way to let non technical users
key
in 3 values on a sheet and have the sheet calculate the 4th value. It is
a
very easy calculation, but I'm not sure how to allow for any of the three
values to be entered and calculate the fourth. Here are the values

A1 Revenue (in dollars)
A2 Variable Costs (percentge)A
3 Fixed Costs (In dollars)
A4 Profit

The formula for A4 is =A1*(1-A2)-A3 (This gives the profit).

My problem is that my boss wants his employees to be able to change the
value of A4 (the profit) and let the system recalculate A1, A2 or A3
depending on which one they leave blank!

Any Ideas?