March commission challenge

[alphadown]

Hi, I'm new here obviously.
So far, I've used Excel a bit at uni and work, I like working with it, but I've never done anything as tricky as this commission thing before. It was a good challenge for me, but probably quite easy for many others here.

The file I sent in had 4 added rows for working, but later on I managed to make a pretty crude looking formula to do it all in one row.

Is it ok to discuss it or post our methods yet, or do we need to wait for the winners to be announced?

 

[MrExcel]

Sure - discuss your methods.
Over 500 people sent in Challenge answers! It will take a bit of time to go through those. (Actually, we are thinking about an automated way to gather the formulas used in each workbook...)

 

[Bob Rooney]

Show us what you've got.

I posted my entry (too late for submission) in the linked thread.

http://www.mrexcel.com/board2/viewtopic.php?t=268080

 

[Lewiy]

Out of the 500, it would be interesting to see how many of us came up with exactly the same answer. There can't be that many ways of doing it, surely.

 

[alphadown]

I just realized mine doesn't work if 2000 is passed in January

In B10 then copy across
=IF(((SUM($B9:B9))-$E$5)<0,B9*$F$5,(B9*$F$5)-(((IF((SUM($B9:B9))-$E$5<0,0,(SUM($B9:B9))-$E$5))-(IF((SUM(A9:$B9))-$E$5<0,0,(SUM(A9:$B9))-$E$5)))*($F$5-$G$5)))

 

[Andrew Fergus]

Hi alphadown and welcome to MrExcel!

Here's my submission:

=ROUND((MIN(B9,MAX(SUM($B9:B9)-$E5,0))*$G5)+(MIN(B9,MAX($E5-SUM($A9:A9),0))*$F5),2)

once again, in B10 and copied across.

Andrew