# Excel formula help

#### GREGGPRATT

##### New Member
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Harvey

##### Well-known Member
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.

#### Oaktree

##### MrExcel MVP
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.

#### yee388

##### Well-known Member
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.

#### GREGGPRATT

##### New Member
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

#### GREGGPRATT

##### New Member
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

#### yee388

##### Well-known Member
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.

Replies
14
Views
280
Replies
33
Views
294
Replies
4
Views
41
Replies
1
Views
175
Replies
15
Views
177

### Forum statistics

1,176,435
Messages
5,903,112
Members
435,010
Latest member
bench205 ### 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?    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