Complex Formula, Error 504 Help!

[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!

 

[bruderbell]

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.

Are you seeking just the total points?

 

[c_m_s_jr]

Perhaps you can get this formula to work as the example below is calculation commissions after a threshold is met. The commision is tier based though.

   A                      B                         C                   
 1 Threshold                             10,000.00                      
 2 Amount on first 10K    50%                                           
 3 Amount after 10K       35%                                           
 4                        Curr PPD EE Contr         YTD EE Contribution 
 5 Employee A             408.66                    3870.24             
 6 Employee B             965                       9600                
 7                                                                      
 8                                                                      
 9 Commission Calculation                                               
10 Employee A             204.33                                        
11 Employee B             397.75                                        
Sheet3
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
B10:B11 =(($B$2*$B5)-(($B$2-$B$3)*MEDIAN(0,B5,(SUM(B5:C5)-$B$1))))
[Table-It] version 09 by Erik Van Geit

The forumla is broken down into 2 parts. The first part ($B$2*$B6)
multiplies the entire current period by the first threshold percent. The second
part of the formula (($B$2-$B$3)*MEDIAN(0,B6,(SUM(B6:C6)-$B$1))) takes
the delta between the two matches ($B$2-$B$3) and multiplies that by any
dollar amount over the threshold. To determine the dollar amount over the
threshold the MEDIAN function is used.
The MEDIAN function returns the middle number in a set. The set of
numbers evualted is 0, the current pay period ($965), and the difference
between the YTD+Cur PD less the threshold (565). The number in the
middle is 565. If the threshold has not been met then 0 will be returned as
the median.
Once the MEDIAN is found it can be multiplied by the difference between
both match percents 84.75 ((50%-35%)*(565)). The result represents the amount
over matched by the first part of the formula which matched the ENTIRE
period amount by the first match (965*50%).
With the overmatch found it can be subtraced from the first part of the formula to
get the total match for the period ($482.5)-(84.75) = $397.75
NOTE: In order to perform this calculation you must have the YTD information to know if the threshold for the year has been met.

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

 

[c_m_s_jr]

As a follow up. I pulled that formula from a "Challenge of the month" from this website. Many different solutions. You might want to check them out. You can also download the workbooks to see the formuals in action.

Excel Problems - Win Fame and Fortune Solving Excel Problems

 

[bruderbell]

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!

This formula is yielding the correct total:
=IF(SUM(M2:T2)>=L2,(SUM(M2:T2)-L2)*4+L2*2,0)

If you have an array where you want to show the monthly points earnings, with months showing points as soon as the total is over the threshhold, you can try this.
In the left-most cell:

=IF(SUM(M2:T2)>=L2,M2*2,0)


In the next cell to the right, and then dragged all the way over:

=IF(SUM($M$2:$T$2)>=$L2,IF((SUM($M$6:M6)+N2*2)<=$L$2*2,N2*2,((F2-SUM($M$2:M2))*2)+(N2-(F2-SUM($M$2:M2)))*4),0)

I really need to install a cool add-in that will let me post my worksheet, but for now this'll have to do.

Please let me know if you find this useful.

 

[Marcelo Branco]

See if this helps

L​

M​

N​

O​

P​

Q​

R​

S​

T​

U​

V​

W​

X​

Y​

1​

Quota​

Jan​

Feb​

Mar​

Apr​

May​

Jun​

Jul​

Aug​

Sep​

Oct​

Nov​

Dec​

Points​

2​

150000​

11177​

20433​

12875​

22655​

32664​

17451​

18397​

14647​

301196​

<tbody>
</tbody>

Formula in Y2
=IF(SUM($M2:$X2)<=$L2,2*SUM($M2:$X2),2*$L2+4*(SUM($M2:$X2)-$L2))

M.

 

[bruderbell]

As a follow up. I pulled that formula from a "Challenge of the month" from this website. Many different solutions. You might want to check them out. You can also download the workbooks to see the formuals in action.

Excel Problems - Win Fame and Fortune Solving Excel Problems

That formula is so much more elegant than mine...

 

[kennedy_clan]

If you need to show the monthly points total then use (in Jan column):

=IF(SUM($M2:M2)<=$L2,(SUM(M2)*2),IF(AND(SUM($M2:M2)>$L2,(SUM($M2:M2)-M2>$L2)),M2*4,((M2-(SUM($M2:M2)-$L2))*2)+((SUM($M2:M2)-$L2)*4)))

and then copy this along the months. otherwise for just a total use:

=IF(SUM($M2:T2)>$L2,($L2*2)+((SUM($M2:T2)-$L2)*4),(SUM($M2:T2)*2)) - you may want to expend the formula to include the remaining months of the year.

Regards