# 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 find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.

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.

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.

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

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

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
2
Views
233
Replies
1
Views
239
Replies
2
Views
205
Replies
2
Views
289
Replies
0
Views
124

1,220,009
Messages
6,151,438
Members
451,028
Latest member
greekness1

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