"or" functions that can be one set of data or another..

[Haden7]

I am trying to make a sheet showing calculations of any 2 variables I put in. If you look at this link you can see all those calculations that give you any of the 4 things if you have 2 of the others. So in the end, all I want is for the top row to say volts amps ohms watts, and you put in 2 variables under it, then you have the same 4 headers only going vertically down a column and using those 2 variables, it calculates and shows what everything is. Obviously the 2 variables I put in would just be copied to the same 2 variables. Heres what I mean, \1

You can see how I put in 2 things on the top row and got all the results on the column. I don't know how to do it. I tried something like =b5*b2 or b5/b3 but it doesnt know what "or" means so I have no clue.

[NBVC]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[NBVC]

Have a look at the attached.

Basically you create a table on the side of each of the 3 formulas for each variable as in the pie chart...

This will give you either #DIV/0 error, 0 or a value...

Then the formula in B4 first looks to see that 2 values are entered, then it looks to see if the value you are looking for is entered, if it is, it just enters it in, otherwise, it looks in the table of results for the numeric result and brings that over.

Please Login or Register  to view this content.

all you need to do is enter the rest of the formulas from the pie chart as per the ones I entered for Volts.

EDIT: I completed the table and formulas for you.

[Haden7]

Ah. I can use that and all, but how in the world did you come up with the formula in B4? Is that part of the what-if analysis thing or what? Or is it just something you "know"? Thanks for the formulas!

[NBVC]

They are just formulas.. no what-if analysis.

It is basically a nested IF formula

First check if only 2 values entered in row 3, if yes, check if the value associated with the header in A4 is entered, if yes, take that number, if no, lookup in the table to get the appropriate result.

The LOOKUP() function checks for a 2 in any array of 1's and #DIV/0 errors.. that are the results of evaluating the combination of conditions.. ($J$2:$J$13=A4)*(ISNUMBER($K$2:$K$13)*($K$2:$K$13<>0) which checks first that J2:J13 equals the correct item (i.e. A4 value), then that there is a number in the corresponding cell in column K and that the number is not a 0.0.. this results in an array of TRUE and FALSE results. 1 is divided by each element of this array to yield a bunch of 1's (1 divided by TRUE is 1) and #DIV/O errors (1 divided by FALSE is error)...

the LOOKUP looks for a 2 in this array and not finding one it returns the last 1 it finds (because it is the last item that is less than or equal to the lookup value of 2)... then it returns the corresponding item from the adjacent column.

Hope that helps.

[Haden7]

Is there a way you can change it to make it work with openoffice? Those formulas seem to only work on excel and I sent it to a guy for him to use it just to find out he has openoffice which seems to not support lookup. So what other formulas would work?

I was starring at that formula and maybe I am wrong but it appears that it has the entire J range listed for B4-B7. So if you are wanting B4 (volts) I think it looks at the entire range of J for the ones named volts, then uses only the corresponding formulas. So if I set each B4-B7 thing individually to only use the 3 formulas that apply with it, wouldn't that get rid of the lookup and make it work on openoffice? I don't really know how to formulate such a thing though.

[NBVC]

according to this site , Lookup is available in OO Calc.

http://wiki.services.openoffice.org/...ed_by_category

I know in OO you need to replace all comma argument separators with semicolons.

Try that.

[Haden7]

That didn't work but I found a way around it. Thanks

[squiggler47]

I took a different approach here are the sheets one in excel one in open office (open office is zipped)

VOLTS :-
=CHOOSE(SUMPRODUCT((B2:E2<>"")*(2^(5-COLUMN(B2:E2)))),,,SQRT(E2*D2),,E2/C2,D2*C2,,,B2,B2,,B2,,,,)
amps:
=CHOOSE(SUMPRODUCT((B2:E2<>"")*(2^(5-COLUMN(B2:E2)))),,,SQRT(E2/D2),,C2,C2,,,E2/B2,B2/D2,,C2,,,,)
ohms
=CHOOSE(SUMPRODUCT((B2:E2<>"")*(2^(5-COLUMN(B2:E2)))),,,D2,,E2/C2^2,D2,,,B2^2/E2,D2,,B2/C2,,,,)
watts
=CHOOSE(SUMPRODUCT((B2:E2<>"")*(2^(5-COLUMN(B2:E2)))),,,E2,,E2,D2*C2^2,,,E2,B2^2/D2,,B2*C2,,,,)