Another Excel question. (1)

The Coffee Shop
1

Is there anyway to set up excel to solve for the empty cell? Let’s say you have a problem where you could have any 4 of 5 factors and the 5th factor needs to be found. However the 4 factors you have aren’t always the same.

Example.
A + B + C + D = E. Sometimes you have A, B, C & D. Sometimes you have A, B, C and E and so forth.

~Matt

2

There is probably a much more elegant way (or you could role this all into one cell) but the easiest way would be to create your cells (A, …, E) and then enter your data. Below or to the right, you could create a cell that solves for that cell if it is empty (so if(A1=0,equation to solve,“”)). One of these for each possible unknown and once you enter your four knowns, the fifth will be solved.

Shane

3

http://www.clemson.edu/ces/phoenix/tutorials/excel/algebra.html

This might help. . . .

4

You could do a bunch of nested IF statements. IF(a1=0, solve for a1,IF(b1=0, solve for b1 etc.

5

I wouldn’t use if(a1=0,do something, do something else) because there is a possibility that one of the non-blank cell values is actually zero. Better to use if(isblank(a1), do something, do something else)

6

I wouldn’t use if(a1=0,do something, do something else) because there is a possibility that one of the non-blank cell values is actually zero. Better to use if(isblank(a1), do something, do something else)

Good point - I forgot about the blank command. I think when I’ve done something like that I’ve used =“” instead of =0.

Shane

7

If the problem is simply an addition one as described, then the cleanest way is to put this into column F.

=if(isblank(e1),sum(a1:d1),e1-sum(a1:d1))

8

Or what you do is you have another sheet linking your original data set. then recreate your data and add in the blanks by putting in

=if(A1>0,A1, =E1-(B1+C1+D1))

and so forth…

9

Nested if statements, like a few have said. If A1 is empty, do this formula, if B1 is empty, do this other formula, if C1 is empty….etc.