Excel formula help

GREGGPRATT

New Member
Joined
Sep 27, 2006
Messages
5
I want to create a spreadsheet but am having problems,

In the simplest way of describing. it would be like this:

A=B+C+D

I can set up a program that B+C+D would equal A, but what if I knew A and inputed A, C, and D. How would I write the formula so that no matter where I inputed the information it would pop up the correct answer in the unknown cell?

I am using A=B+C+D as an example and could follow this as a template.

Thanks

Gregg
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
This cant be done using formulas. You can, however, calculate the difference if you know which variable is unknown. However, if you are not afraid of using VBA code, you can build a function that automaticly changes the missing value(s).
I guess your "real" formula is probably very complicated, but if you can post the solutions to finding the a's, b's, c's and d's, I can work it in a function for you.
 
Upvote 0
Another option is to set up your A=B+C+D formula in A. Then, use tools --> goal seek to set A to a specific value by changing B.
 
Upvote 0
Here's a formula you can copy down the E column to find out what the missing variable value is:
Book1
ABCDE
110613
2891027
3205510
415645
Sheet1


But like Harvey said, to do exactly what you asked about would require a circular reference, and cannot be done using formulas.
 
Upvote 0
This cant be done using formulas. You can, however, calculate the difference if you know which variable is unknown. However, if you are not afraid of using VBA code, you can build a function that automaticly changes the missing value(s).
I guess your "real" formula is probably very complicated, but if you can post the solutions to finding the a's, b's, c's and d's, I can work it in a function for you.

E=P/I E=IR E=SQRT(PR)
I=P/E I=E/R I=SQRT(P/R)
R=E²/P R=E/I R=P/I²
P=E²/R P=IE P=I²R

E I R P

4 boxes total,
Enter at least 2 of your known variables and have excel come up with the other two.

Thanks

Gregg
 
Upvote 0
Actual formula for problem

This cant be done using formulas. You can, however, calculate the difference if you know which variable is unknown. However, if you are not afraid of using VBA code, you can build a function that automaticly changes the missing value(s).
I guess your "real" formula is probably very complicated, but if you can post the solutions to finding the a's, b's, c's and d's, I can work it in a function for you.

E=P/I E=IR E=SQRT(PR)
I=P/E I=E/R I=SQRT(P/R)
R=E²/P R=E/I R=P/I²
P=E²/R P=IE P=I²R

E I R P

4 boxes total,
Enter at least 2 of your known variables and have excel come up with the other two.

Thanks

Gregg
 
Upvote 0
Try a separate table like this:
Book1
ABCDEFGHI
1EIRPEIRP
2105010500.2500
3500.210500.2500
40.250010500.2500
51050010500.2500
Sheet1


Formulas:
F2: =IF(AND(A2="",B2<>"",C2<>""),B2*C2,IF(AND(A2="",B2<>"",D2<>""),D2/B2,IF(AND(A2="",C2<>"",D2<>""),SQRT(C2*D2),IF(A2<>"",A2,"ERROR"))))
G2: =IF(AND(B2="",A2<>"",C2<>""),A2/C2,IF(AND(B2="",C2<>"",D2<>""),SQRT(D2/C2),IF(AND(B2="",A2<>"",D2<>""),D2/A2,IF(B2<>"",B2,"ERROR"))))
H2: =IF(AND(C2="",A2<>"",B2<>""),A2/B2,IF(AND(C2="",B2<>"",D2<>""),D2/B2^2,IF(AND(C2="",A2<>"",D2<>""),A2^2/D2,IF(C2<>"",C2,"ERROR"))))
I2: =IF(AND(D2="",A2<>"",B2<>""),A2*B2,IF(AND(D2="",B2<>"",C2<>""),B2^2*C2,IF(AND(D2="",A2<>"",C2<>""),A2^2/C2,IF(D2<>"",D2,"ERROR"))))

...and copy down

There is surely a more elegant way, but this might get you started.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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