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)

Thanks in advance.
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
Bob Phillips
09-21-2010, 03:47 AM
Error in your formula

=($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