| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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: |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
