Complex Formula, Error 504-Can't find where I went wrong

[paziraj]

Good morning everyone,

I just found this site and I'm hoping someone here can help me!

Basically, I run a points rewards program for the company I'm with and built a complex formula to determine points earned for our customers. I'll give you a quick breakdown of how the points work before showing the formula.

Every customer is given a base quota. This is the amount of money they need to spend in order to be eligible to earn points.
Once they reach and exceed the base quota, their sales all the way back to January are then accounted for to earn points.

The customer earns 2 points for every 1 dollar they spend up to the base quota. Once they exceed this quota, they begin to earn 4 points for every dollar they spend thereafter.

The issue I'm having is the month in which they have exceeded this base quota. I had written it in plain text on how it should be laid out then converted it to a formula. This is what I came up with:

=IF(SUM($M2:T2)>$L2,(IF(SUM(SUM($M2:S2))>$L2,T2*4,(SUM((SUM($M2:T2)-$L2)*4)+(SUM($T2-(SUM(M2:T2)-$L2*2)))),T2*2)))
Please note that columns M to T are customers monthly sales from January to August, column L is their base quota.

=IF(SUM(Jan:Jul)>Basequota,(IF(SUM(SUM(Jan:Jul))>Basequota,Aug*4,(SUM((SUM(Jan:Aug)-Basequota)*4)+(SUM(Aug-(SUM(Jan:Aug)-Basequota*2)))),Aug*2)))

The Bold, Red portion of the formula is where I KNOW I'm having the error.

Basically, this portion needs to say: Total January to August sales minus the base quota times 4 (For the 4pts per dollar spent above base quota) PLUS August's Sales minus (Jan-Aug Sales minus the base quota), times two.

Any help you can provide would be fantastic!

Here is a sample of a customer's information to test if the formula is accurate.

Base quota: 150,000
January Sales: 11,177
February Sales: 20,433
March Sales: 12,875
April Sales: 22,655
May Sales: 32,664
June Sales: 17,451
July Sales: 18,397
August Sales: 14,647

This customer has YTD sales of 150,299. Their points total SHOULD equal 301,196. August's points should be 29,892

Thanks for your help!

[Dexter1759]

If you don't require a monthly breakdown and just a total perhaps the following would do you....


=(MIN(BaseQuota,SUM(YTDSales))*2)+(MAX(SUM(YTDSale s)-BaseQuota,0)*4)

Where YTDSales is a range representing Jan to Aug sales.

I like avoiding IFs where I can because it can end up being confusing when you come back to read/edit it at a later date, and tends to lead to near duplication between the TRUE and FALSE parts, which can also be a pain for future changes.

Of course, it's a slightly different matter if you require a monthly breakdown. In which case I suggest an "Points Balance" column into the data that shows a per month points and then sum that column for the total.