Or Function

J

JimS

I'm writing a long and complicated formula, so to shorter it up and
"OR" statement would probably help, but I get confused with the
syntax. So I'll start at the beginning; how would I combine this
formula using or...

If(c16="c",b16*d16,if(c16="d",b16*d16))

Thanks
 
D

Dave Peterson

=if(or(c16="c",c16="d"),b16*d16,"what goes here")
or
=if(or(c16={"c","d"}),b16*d16,"what goes here")
 
J

JimS

In the second formula, I take it those "curly brackets" surrounding
{"c","d"} are important?
 
F

francis

Hi Jim

these {"c","d"} is an array and is a shorter way to the first formula
on the specified criteria in your example
the formula will be evaluating if cell C16 is "c" or "d" and if they are
b16*d16 will apply otherwise it will return "what goes here"


--
Hope this is helpful
Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis
 
J

JimS

Thanks, that helps. So far so good, but I'm having a problem. Here
is the first part of my formula, which works fine.

=IF($B$16>7,"ERROR",IF($D$16>7,"ERROR",IF($D$16="",$E$16*-50,IF($D$16<$B$16,"ERROR",IF(OR($C$16={"c","d"}),IF($B$16=7,$D$16*20+
300+1000,IF($B$16>=6,$D$16*20+300+500,IF($B$16>=5,$D$16*20+300,IF($B$16<5,$D$16*20+50)))))))))

To finish it, I'm trying to add the following to this formula; but it
always returns false. Both formulas work as a stand alone, but not
combined (at least the way I'm doing it.)
Obviously I'm doing something wrong.


=IF(OR($C$16={"h","s"}),IF($B$16=7,$D$16*30+300+1000,IF($B$16>=6,$D$16*30+300+500,IF($B$16>=4,$D$16*30+300,
IF($B$16<4,$D$16*30+50

What is the proper way to combine these two sections?

Thanks again.
 
J

JimS

It took a while but I think I got it. It has to do with the number of
parenthesis between the two sections:

=IF($B$16>7,"ERROR",IF($D$16>7,"ERROR",IF($D$16="",$E$16*-50,IF($D$16<$B$16,"ERROR",IF(OR($C$16={"c","d"}),
IF($B$16=7,$D$16*20+300+1000,IF($B$16>=6,$D$16*20+300+500,IF($B$16>=5,$D$16*20+300,IF($B$16<5,$D$16*20+50)))),

IF(OR($C$16={"h","s"}),IF($B$16=7,$D$16*30+300+1000,IF($B$16>=6,$D$16*30+300+500,IF($B$16>=4,$D$16*30+300,
IF($B$16<4,$D$16*30+50))))))))))
 
Top