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

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
Joined
Nov 18, 2004
Messages
953
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
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
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
Joined
Mar 7, 2004
Messages
1,374
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
Joined
Sep 27, 2006
Messages
5

ADVERTISEMENT

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
Joined
Sep 27, 2006
Messages
5
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
Joined
Mar 7, 2004
Messages
1,374
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.
 

Forum statistics

Threads
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.
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
Top