Excel: IF and or....... 1

[coolcarls]

My sheet is...
A1=Truck or Car
A2=1 or 2

So I need the if, and or deal to figure all the variables in A3. So the combination could be A1=Truck and A2=2 or A1=Truck and A2=1 or A1=Car and A2=1 .... I know this can't be that difficult, but I can't seem to get all together. I have it where 2 of the 3 variables work. And if this is possible, how easy is it to add more variables, like B3=Boat.
Here's as far as I got, what a dummy

=IF((AND(A1="Truck",A2="0")),120,IF(A1="Truck",115,IF(A2="Car",36)))

And if there's a simple answer, how do you calculate where and how many parentheses.



Thanks for anything


Carl

 

[Zathras]

Like this?

=IF(A1="Car",36,IF(A2="0",120,115))

 

[coolcarls]

No not really, almost. What if A1="Truck" And A2=1.
Sorry to be a pain!
Carl

 

[KenWright]

List a table with all the combinations and then use SUMPRODUCT to query it:-

Assuming your data table is in J2:L11 eg:-

J K L
2 Truck 0 120
3 Truck 1 130
4 Truck 2 140
5 Truck 3 150
6 Truck 4 160
7 Car 0 170
8 Car 1 180
9 Car 2 190
10 Car 3 200
11 Car 4 210

then just use

=SUMPRODUCT(($J$2:$J$11=A1)*($K$2:$K$11=A2)*($L$2:$L$11))

Note, do NOT include any headers in the ranges, as a text header in the value range L2:L11 will return an error.

Regards
Ken........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Zathras]

=IF(A1="Car",36,IF(A2="0",120,115))

No not really, almost. What if A1="Truck" And A2=1.

Then A1 would not be "Car" in which case the second part of the outer IF would take effect:

IF(A2="0",120,115)

and since A2 is not "0" then the second part of the inner IF would take effect, yielding the answer of 115.

But the burden is still on you to indicate what you would want for each of the 4 combinations. (Hard to tell from your original formula, especially where you are testing A2 for "Car" when A2 should only be a "0" or "1" since you only indicated three possible outcomes: 120, 115 or 36.)

As Ken suggested, it might be easiest to have an explicit table for the all of the possibilities that exist. (Although I would use DGET or VLOOKUP instead of SUMPRODUCT since I prefer fitting the tool to the application. There is no intrinsic multiplying or adding in what you are doing.)

 

[KenWright]

LOL - Stop picking on my SUMPRODUCT you ol meanie you <g>

Another option would be to create the table, concatenate them all in a final but one column (Values in final), and then just use VLOOKUP, eg with my previous example, values would shift to M and concatenated data would sit in L, and then just use

=VLOOKUP(A1&A2,$L$2:$M$11,2,0)

or............. you could just use DGET (Did someone already suggest that??)

Regards
Ken......

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[coolcarls]

TTTttttthhaaannks!!