PDA

View Full Version : Solved: Help with a formula please

ioncila
09-21-2010, 12:46 AM
Hi
I have this formula that I wish to get it shorter if possible. Is there some way to do that?

=IF(AND(\$D35="A";\$Q\$24=BV\$31);\$AW35;0)+IF(AND(\$D35="A";\$Q\$23=BV\$31);\$AY35;0)+IF(AND(\$D35="A";\$Q\$22=BV\$31);\$BA35;0)+IF(AND(\$D35="A";\$Q\$21=BV\$31);\$BC35;0)+IF(AND(\$D35="A";\$Q\$20=BV\$31);\$BE35;0)+IF(AND(\$D35="A";\$Q\$19=BV\$31);\$BG35;0)+IF(AND(\$D35="A";\$Q\$18=BV\$31);\$BI35;0)+IF(AND(\$D35="A";\$Q\$17=BV\$31);\$BK35;0)+IF(AND(\$D35="A";\$Q\$16=BV\$31);\$BM35;0)+IF(AND(\$D35="A";\$Q\$15=BV\$31);\$BO35;0)

macropod
09-21-2010, 01:24 AM
hi ioncila,

You could start by changing your formula to:
=IF(\$D35="A";IF(\$Q\$24=BV\$31;\$AW35;0)+IF(\$Q\$23=BV\$31;\$AY35;0)+IF(\$Q\$22=BV\$31;\$BA35;0)+IF (\$Q\$21=BV\$31;\$BC35;0)+IF(\$Q\$20=BV\$31;\$BE35;0)+IF(\$Q\$19=BV\$31;\$BG35;0)+IF(\$Q \$18=BV\$31;\$BI35;0)+IF(\$Q\$17=BV\$31;\$BK35;0)+IF(\$Q\$16=BV\$31;\$BM35;0)+IF(\$Q\$15 =BV\$31;\$BO35;0);0)

And, if the values in \$AW35 etc must always be numeric, you could simplify further, to:
=(\$D35="A")*((\$Q\$24=BV\$31)*\$AW35+(\$Q\$23=BV\$31)*\$AY35+(\$Q\$22=BV\$31)*\$BA35+(\$Q\$21=BV\$31 )*\$BC35+(\$Q\$20=BV\$31*\$BE35)+(\$Q\$19=BV\$31)*\$BG35+(\$Q\$18=BV\$31)*\$BI35+(\$Q\$17= BV\$31)*\$BK35+(\$Q\$16=BV\$31)*\$BM35+(\$Q\$15=BV\$31)*\$BO35)

macropod
09-21-2010, 01:58 AM
x

xld
09-21-2010, 03:47 AM

=(\$D35="A")*((\$Q\$24=BV\$31)*\$AW35+(\$Q\$23=BV\$31)*\$AY35+(\$Q\$22=BV\$31)*\$BA35+(\$Q\$21=BV\$31 )*\$BC35+(\$Q\$20=BV\$31)*\$BE35+(\$Q\$19=BV\$31)*\$BG35+(\$Q\$18=BV\$31)*\$BI35+(\$Q\$17= BV\$31)*\$BK35+(\$Q\$16=BV\$31)*\$BM35+(\$Q\$15=BV\$31)*\$BO35)

macropod
09-21-2010, 03:55 AM
Hi xld,

Well spotted!

ioncila
09-21-2010, 04:17 AM
Hi
Thanks for help Macropod.
I was testing your suggestions, Thats why I didnt notice quickly.

First suggestion works fine.
Second one returns #Value in third segment of the formula
TRUE*(0+(\$Q\$23=#VALUE)*\$AY35+(\$Q\$22=BV\$31)*\$BA35+(\$ Q\$21=BV\$31)*\$BC35+(\$Q\$20=BV\$31*\$BE35)+(\$Q\$19=BV\$31)*\$BG35+(\$Q\$18=BV\$31)*\$BI 35+(\$Q\$17=BV\$31)*\$BK35+(\$Q\$16=BV\$31)*\$BM35+(\$Q\$15=BV\$31)*\$BO35)

\$AW35 etc are numeric.
BV\$31 has values like "AO1", "AO2", etc

One last note - Excel version is 2003

Thanks

ioncila
09-21-2010, 04:25 AM
Only now I saw XLD correction.

It works.

Thank you very much.

Ioncila