# 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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### 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
5
Views
79
Replies
9
Views
165
Replies
3
Views
159
Replies
6
Views
148
Replies
3
Views
264

### Forum statistics

1,141,625
Messages
5,707,470
Members
421,510
Latest member
haroonstr ### 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.

### Which adblocker are you using?    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

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