Challenge of the Month for March/April 2007

[UniMord]

Mr. Excel nominated as best formula:

To get started, I will nominate this formula by Laurie Fullerton:
=SUMPRODUCT((SUM($B9:M9)>{0;2000})*(SUM($B9:M9)-{0;2000})*{1.5;-0.25})-SUM($B10:L10)

I use SUMPRODUCT all the time, but this one's got me stumped. I would be most appreciative if someone were to offer an explanation of what's going on here.

 

[Bean Counter]

The SUMPRODUCT is superfluous. The formula works fine with loping off the SUMPRODUCT and just starting with the equal sign and two opening parentheses.

The rest of the formula is simply sublime in the execution of the logic with the array formulas. I have been studying this formula on and off for the last two days and am still amazed at its' construction. I would never have come up with a solution so elegant. So a tip of the green eyeshades to Laurie from me!

 

[Bean Counter]

The SUMPRODUCT is superfluous.

Oops. Nope, I was wrong. SUMPRODUCT is necessary for Excel to evaluate the arrays properly.

The best way to is analyze the formula is to step through it using the "evaluate formula" function in formula auditing. Many times. On multiple cells (especially May).

 

[UniMord]

Um, not so fast...

Lopping off the SUMPRODUCT as you suggested will cause the formula to fail when it hits the threshold month (May).

Since you seem to know what's going on here, perhaps you can offer a concise explanation of the logic. As I confessed above, I'm stumped.

Thanks.

 

[UniMord]

Oh, you beat me to it. Sorry.

Thanks for the suggestion. The evaluate formula spells out all the magic tricks. No secrets anymore! Ha ha! Now I know everything! I can take over the world!

 

[ExcelChampion]

I briefly looked at it and initially it seems like there must be helper cells. But, I may be mistaking.

 

[UniMord]

There are no helper cells, but her formula in B10 is different than in C10:M10 (which doesn't show on the challenge page).

In B10 she used:

=SUMPRODUCT((SUM($B9:B9)>{0;2000})*(SUM($B9:B9)-{0;2000})*{1.5;-0.25})

In C10:M10 she used:

=SUMPRODUCT((SUM($B9:C9)>{0;2000})*(SUM($B9:C9)-{0;2000})*{1.5;-0.25})-SUM($B10:B10)

If she'd have used:

=SUMPRODUCT((SUM($B9:B9)>{0;2000})*(SUM($B9:B9)-{0;2000})*{1.5;-0.25})-SUM(A10:$A10)

she could've used the same formula throughout (since A10 has no number, this subtracts 0 in B10).

 

[ExcelChampion]

There are no helper cells, but her formula in B10 is different than in C10:M10 (which doesn't show on the challenge page).

In B10 she used:

=SUMPRODUCT((SUM($B9:B9)>{0;2000})*(SUM($B9:B9)-{0;2000})*{1.5;-0.25})

In C10:M10 she used:

=SUMPRODUCT((SUM($B9:C9)>{0;2000})*(SUM($B9:C9)-{0;2000})*{1.5;-0.25})-SUM($B10:B10)

If she'd have used:

=SUMPRODUCT((SUM($B9:B9)>{0;2000})*(SUM($B9:B9)-{0;2000})*{1.5;-0.25})-SUM(A10:$A10)

she could've used the same formula throughout (since A10 has no number, this subtracts 0 in B10).

Well, to me, that defeats the purpose of the challenge! I could write different formulas for each month and get the correct result. I figured the point was to come up with ONE formula that could be dragged across with the use of helper cells to do INTERMEDIATE calculations.

For instance, I came up with

=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,(M9-MOD(SUM($B$9:M9),$E$5))*$F$5+(M9-(M9-MOD(SUM($B$9:M9),$E$5)))*$G$5))

May not be the most elegant, but I believe it is more true to the meaning of the challenge than Laurie's because I have ONE formula.

Not to say that Laurie's formula or approach isn't worthy of praise, but still, I just think having mulitple formulas that are non-helper isn't staying true to the challenge.

 

[barry houdini]

Laurie's formula uses the same method as that described here

I'd say there were better ways of achieving that here because there are only 2 tiers but certainly useful when you have many.....