Solved: Help with a formula please

[ioncila]

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);$BK3 5;0)+IF(AND($D35="A";$Q$16=BV$31);$BM35;0)+IF(AND($D35="A";$Q$15=BV$31);$BO 35;0)

Thanks in advance.

[macropod]

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;$B G35;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)*$BI 35+($Q$17=BV$31)*$BK35+($Q$16=BV$31)*$BM35+($Q$15=BV$31)*$BO35)

[macropod]

x

[Bob Phillips]

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)*$BI 35+($Q$17=BV$31)*$BK35+($Q$16=BV$31)*$BM35+($Q$15=BV$31)*$BO35)

[macropod]

Hi xld,

Well spotted!

[ioncila]

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]

Only now I saw XLD correction.

It works.

Thank you very much.

Ioncila