I am having trouble with the IF function and a compound condition

R

rub

I want to use the IF function to set a value of 54 if A100=369 and
B100="Gas" or A100=391 and B100="Gas". Otherwise the value should be
18.

I used =IF((A100=369 and B100="Gas") or (A100=391 and
B100="Gas"),54,18) and got an error.

I also used =IF(B100="Gas" and (A100=369 or A100=391), 54,18) and also
got an error.

Any ideas?
 
C

CLR

=IF(OR(AND(A100=391,B100="gas"), AND(A100=369,B100="gas")),54,18)

Vaya con Dios,
Chuck, CABGx3
 
D

Dave Peterson

One way is to translate your requirements word for word:
=if(or(and(a100=369,b100="Gas"),and(a100=391,b100="Gas")),54,18)

Another way that's equivalent Since B100 has to be Gas for both 369 and 391:
=if(and(b100="gas",or(a100=369,a100=391)),54,18)


And another way to write that formula that makes it easier if the number of
values in A100 can get bigger:

=if(and(b100="gas",or(a100={369,391})),54,18)
 
R

rub

Thanks it works.


Dave said:
One way is to translate your requirements word for word:
=if(or(and(a100=369,b100="Gas"),and(a100=391,b100="Gas")),54,18)

Another way that's equivalent Since B100 has to be Gas for both 369 and 391:
=if(and(b100="gas",or(a100=369,a100=391)),54,18)


And another way to write that formula that makes it easier if the number of
values in A100 can get bigger:

=if(and(b100="gas",or(a100={369,391})),54,18)
Click to expand...
 
R

rub

I have a different twist on the same question. For some cells I need
to leave the current value in the cell alone if the IF premise if
false. So in other words instead of the value being 18, I would like
to not change the value of the cell. Any ideas?
 
C

CLR

It depends on what "some cells" is, how many there are, and what values you
wish displayed.........in other words, we need more details........

Vaya con Dios,
Chuck, CABGx3
 
M

Max

Maybe you mean something like this in say, D100:
=IF(C100="","",IF(AND(OR(A100={369,391}),B100="Gas"),54,C100))

Provided C100 isn't empty (this is just a first check done on C100), D100
will then return the value in C100 as the value_if_False (instead of the
previous value of 18)
 
R

rub

I am putting this function in every cell in one column so that if that
row contains 369 and "Gas" then 54 should be the result or else what is
already in the cell.

Address Utility Units
369 Gas 54
1710 Elec 36

The 36 would have already been in the cell. The 54 was added by the
formula
 
R

rub

That will work if I use a new column. Is there anyway to do it in the
existing column
so something like
C100=IF(C100="","",IF(AND(OR(A100={369,391}),B100="Gas"),54,C100))
 
C

CLR

That would require VBA, not "just a formula copied down"..........
If you would rather have a code solution instead of a formula one, post back
and someone will help.

Vaya con Dios,
Chuck, CABGx3
 
M

Max

Is there anyway to do it in the existing column ..

Similar to Chuck's response in the other branch, not possible via formulas.
Hang around awhile for responses from those versed in vba, or try a new
posting in .excel.programming.
 
R

rub

thanks for the help


Similar to Chuck's response in the other branch, not possible via formulas.
Hang around awhile for responses from those versed in vba, or try a new
posting in .excel.programming.
Click to expand...
 
Top