re: a two way equation

[4GONERS]

Hello,
I am a little stumped. my skill level at programming is beginner, so please bear that in mind.
I have an simple equation in which cell B4xE4=H4. What I would like to be able to do, is enter a value into anyone of the three fields, and have the other two update automatically.
with the results, i would like to do on multiple cells on the sheet if possible.
Is such a thing possible?
-4g

[4GONERS]

i guess i meant 3 way equation, since all 3 fields can be edited

[MrShorty]

A sample spreadsheet might help us better understand what you are trying to do.

Assuming I understand what you are asking for and bearing in mind that you are a beginner, I would suggest you abandon exactly what you are asking for. If I understand it, you want these three cells to not only be able to accept input from the user, but you also want these three cells to process the inputs. I am not fond of that kind of structure (it can be done, but I don't think it is a good idea). IMO, a spreadsheet works best when a cell does one thing - either accepts input or processes the input.

Here's how I would approach your problem:
1) Determine a suitable toggle to tell the spreadsheet which value is to be calculated. If the values for a b and c should always be positive numbers, then using a negative number as a toggle might work well. or leave the unkown blank. Or whatever toggle makes sense to you.
2) Input values into B4, E4, H4 -- one of the cells containing the toggle value.
3) In C4, put =IF(isblank(C4),H4/E4,C4)
4) In F4, put =IF(isblank(E4),H4/C4,E4)
5) In I4, put =IF(isblank(H4),C4*E4,H4)

Now you can use B, E, and H as your inputs, and the desired outputs are in C, F, and I.

[shg]

And to that, you could add a second formula that tells you which (missing) variable that formula calculates.

[4GONERS]

A sample spreadsheet might help us better understand what you are trying to do.

Assuming I understand what you are asking for and bearing in mind that you are a beginner, I would suggest you abandon exactly what you are asking for. If I understand it, you want these three cells to not only be able to accept input from the user, but you also want these three cells to process the inputs. I am not fond of that kind of structure (it can be done, but I don't think it is a good idea). IMO, a spreadsheet works best when a cell does one thing - either accepts input or processes the input.

Here's how I would approach your problem:
1) Determine a suitable toggle to tell the spreadsheet which value is to be calculated. If the values for a b and c should always be positive numbers, then using a negative number as a toggle might work well. or leave the unkown blank. Or whatever toggle makes sense to you.
2) Input values into B4, E4, H4 -- one of the cells containing the toggle value.
3) In C4, put =IF(isblank(C4),H4/E4,C4)
4) In F4, put =IF(isblank(E4),H4/C4,E4)
5) In I4, put =IF(isblank(H4),C4*E4,H4)

Now you can use B, E, and H as your inputs, and the desired outputs are in C, F, and I.

1. I don't really understand point 1, selecting a toggle. how do i tell excel which cell is to be calculated.
2 1 tried out your equation, but in cell C4, i get a blue circle next to the intiger, not really sure what that means.

And to that, you could add a second formula that tells you which (missing) variable that formula calculates.

can you please elaborate what you mean by missing variable?

[MrShorty]

1. I don't really understand point 1, selecting a toggle. how do i tell excel which cell is to be calculated.

In the examples I wrote, I assumed that the desired unkown could be indicated by leaving that cell blank.

2 1 tried out your equation, but in cell C4, i get a blue circle next to the intiger, not really sure what that means.

Probably means circular reference (a cell formula referring to itself is a circular reference) because I was careless. The formula for C4 should refer to B4 =IF(isblank(B4),H4/E4,B4)

[4GONERS]

this is great!

now that inputs and outputs are separated, what is the next step?

Do i =(c4*f4), i can't put a forumula inside h4, can I?

[MrShorty]

Correct, the idea here is to not put a formula into h4.

now that inputs and outputs are separated, what is the next step?

I'm not sure. You haven't said what you want to do with these three numbers once you have all three. So, I would say that the next step is to decide what you want to do with these three numbers. Then we should be able to figure out how to accomplish that task.

[4GONERS]

Correct, the idea here is to not put a formula into h4.

I'm not sure. You haven't said what you want to do with these three numbers once you have all three. So, I would say that the next step is to decide what you want to do with these three numbers. Then we should be able to figure out how to accomplish that task.

Oh.
what i wanted to do was multiply b4 x e4 = h4,

the overall goal of the spreadsheet was a unit coversion. however more than two units exist, i wanted to populate them all.
ie. metric (north americal) --> imperial--> metric (europe) --> metric (aus)

[MrShorty]

what i wanted to do was multiply b4 x e4 = h4,

which is exactly what we should have, only the result is in I4. Is it necessary for the result to be in H4? Right now we are trying to use H4 as an input cell, not an output cell. If H4 must be an output cell, are you willing to use I4 as the input cell (or G4 or some other cell)?

[4GONERS]

which is exactly what we should have, only the result is in I4. Is it necessary for the result to be in H4? Right now we are trying to use H4 as an input cell, not an output cell. If H4 must be an output cell, are you willing to use I4 as the input cell (or G4 or some other cell)?

that is correct, h4 is input, i4 is output.
but i am not sure how which cells to use to perform the multiplication of two cells.

[shg]

Please Login or Register  to view this content.

D2 and down:

=CHOOSE(SUMPRODUCT(ISNUMBER(A2:C2)*{1,2,4}) + 1, "Enter two!", "Enter two!", "Enter two!", "Accel", "Enter two!", "Mass", "Force", "Enter only two!")

E2 and down:

=IFERROR(CHOOSE(MATCH(D2, $A$1:$C$1, 0), B2*C2, A2/C2, A2/B2), "")