Something better than OR()
 

=IF(B2="misc",E2,IF(OR(B2*1=I$2,B2*1=I$3,B2*1=I$4, B2*1=I$5,B2*1=I$6,B2*1=I$7,B2*1=I$8,B2*1=I$9,B2*1= I$10,B2*1=I$11,B2*1=I$12,B2*1=I$13,B2*1=I$14,B2*1= I$15,B2*1=I$16,B2*1=I$17,B2*1=I$18,B2*1=I$19,B2*1= I$18,B2*1=I$19,B2*1=I$20,B2*1=I$21,B2*1=I$22,B2*1= I$23),E2,E2*20/18))

Is there a better way to do the above formula? I have experimented with an
array but can't get it to work.
Thanks for any help or suggestions.
Lee

Something better than OR()
 

Try incorporating MATCH into your formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lee" wrote in message
...
=IF(B2="misc",E2,IF(OR(B2*1=I$2,B2*1=I$3,B2*1=I$4, B2*1=I$5,B2*1=I$6,B2*1=I$7,B2*1=I$8,B2*1=I$9,B2*1= I$10,B2*1=I$11,B2*1=I$12,B2*1=I$13,B2*1=I$14,B2*1= I$15,B2*1=I$16,B2*1=I$17,B2*1=I$18,B2*1=I$19,B2*1= I$18,B2*1=I$19,B2*1=I$20,B2*1=I$21,B2*1=I$22,B2*1= I$23),E2,E2*20/18))

Is there a better way to do the above formula? I have experimented with an
array but can't get it to work.
Thanks for any help or suggestions.
Lee

Something better than OR()
 

Try this:

=IF(B2="misc",E2,IF(ISNUMBER(MATCH(B2*1,I$2:I$23,0 )),E2,E2*20/18))


--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
=IF(B2="misc",E2,IF(OR(B2*1=I$2,B2*1=I$3,B2*1=I$4, B2*1=I$5,B2*1=I$6,B2*1=I$7,B2*1=I$8,B2*1=I$9,B2*1= I$10,B2*1=I$11,B2*1=I$12,B2*1=I$13,B2*1=I$14,B2*1= I$15,B2*1=I$16,B2*1=I$17,B2*1=I$18,B2*1=I$19,B2*1= I$18,B2*1=I$19,B2*1=I$20,B2*1=I$21,B2*1=I$22,B2*1= I$23),E2,E2*20/18))

Is there a better way to do the above formula? I have experimented with an
array but can't get it to work.
Thanks for any help or suggestions.
Lee

Something better than OR()
 

Thanks, it works! I don't understand the isnumber part but will read more in
the help file.
Thanks again.
Lee
"T. Valko" wrote in message
...
Try this:

=IF(B2="misc",E2,IF(ISNUMBER(MATCH(B2*1,I$2:I$23,0 )),E2,E2*20/18))


--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
=IF(B2="misc",E2,IF(OR(B2*1=I$2,B2*1=I$3,B2*1=I$4, B2*1=I$5,B2*1=I$6,B2*1=I$7,B2*1=I$8,B2*1=I$9,B2*1= I$10,B2*1=I$11,B2*1=I$12,B2*1=I$13,B2*1=I$14,B2*1= I$15,B2*1=I$16,B2*1=I$17,B2*1=I$18,B2*1=I$19,B2*1= I$18,B2*1=I$19,B2*1=I$20,B2*1=I$21,B2*1=I$22,B2*1= I$23),E2,E2*20/18))

Is there a better way to do the above formula? I have experimented with
an array but can't get it to work.
Thanks for any help or suggestions.
Lee

Something better than OR()
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
Thanks, it works! I don't understand the isnumber part but will read more
in the help file.
Thanks again.
Lee
"T. Valko" wrote in message
...
Try this:

=IF(B2="misc",E2,IF(ISNUMBER(MATCH(B2*1,I$2:I$23,0 )),E2,E2*20/18))


--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
=IF(B2="misc",E2,IF(OR(B2*1=I$2,B2*1=I$3,B2*1=I$4, B2*1=I$5,B2*1=I$6,B2*1=I$7,B2*1=I$8,B2*1=I$9,B2*1= I$10,B2*1=I$11,B2*1=I$12,B2*1=I$13,B2*1=I$14,B2*1= I$15,B2*1=I$16,B2*1=I$17,B2*1=I$18,B2*1=I$19,B2*1= I$18,B2*1=I$19,B2*1=I$20,B2*1=I$21,B2*1=I$22,B2*1= I$23),E2,E2*20/18))

Is there a better way to do the above formula? I have experimented with
an array but can't get it to work.
Thanks for any help or suggestions.
Lee

Something better than OR()
 

ISNUMBER just checks whether the MATCH is working or not. If MATCH finds the
value, it returns its position in the array, the index number. If it
doesn't, it returns #N/A, so ISNUMBER returns TRUE if found, FALSE if not.

--
__________________________________
HTH

Bob

"Lee" wrote in message
...
Thanks, it works! I don't understand the isnumber part but will read more
in the help file.
Thanks again.
Lee
"T. Valko" wrote in message
...
Try this:

=IF(B2="misc",E2,IF(ISNUMBER(MATCH(B2*1,I$2:I$23,0 )),E2,E2*20/18))


--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
=IF(B2="misc",E2,IF(OR(B2*1=I$2,B2*1=I$3,B2*1=I$4, B2*1=I$5,B2*1=I$6,B2*1=I$7,B2*1=I$8,B2*1=I$9,B2*1= I$10,B2*1=I$11,B2*1=I$12,B2*1=I$13,B2*1=I$14,B2*1= I$15,B2*1=I$16,B2*1=I$17,B2*1=I$18,B2*1=I$19,B2*1= I$18,B2*1=I$19,B2*1=I$20,B2*1=I$21,B2*1=I$22,B2*1= I$23),E2,E2*20/18))

Is there a better way to do the above formula? I have experimented with
an array but can't get it to work.
Thanks for any help or suggestions.
Lee

Something better than OR()
 

I have not modified the part below where it quotes the original message. It
is useless. I see a blank message posted by "unknown" at "10/18/2009 3:25 AM
PST".

Compare that with the message by "Bob Phillips" at "10/19/2009 3:50 PM PST",
which quotes three previous messages. Suddenly the subject line makes sense!

What's going on here?

"unknown" wrote: