Complex Formula, Error 504 Help!

paziraj

New member
Joined
Sep 26, 2013
Messages
6
Reaction score
0
Points
0
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!
 
Good morning,

Please see the attached file. Note that the formula changes slightly in format between January and February.

Hope this helps,
 

Attachments

  • Reward_Points_sample.xlsx
    10.2 KB · Views: 13
Oops, one addition. Please replace the last if statement with this : IF($N$3>$A$3,B3*2,0)). I had left off the final condition of the if, so if the person has not reached their quota the formulas will return "False" instead of zero. Replace in Jan and Feb, then drag Feb thru Dec.

Sorry about that.
 
Hmmm...Is there any way I could ask your assistance plugging it in with ALL the columns I require? For some reason when I'm trying to plug it into my own page, because the column names are different it's not translating properly, even when I remove the "$" from the formulas.

I have about 1,000 customers so I need to be sure that I can click and drag these formulas all the way down...

The columns I have are as follows:

A: Branch
B: Account Number
C: Partner Type
D: Distribution Method
E: Customer Name
F: Address 1
G: Address 2
H: City
I: Province
J: Postal Code
K: Sales Rep
L: Base Quota
M-X: Sales January - December
Y: Total YTD Sales
Z: Met base quota? (Y/N formula)
AA-AL: Points January-December
AM: Total Points

That would be a great help!!

Thank you!
 
Hello again,

Try this one on Sheet2.
 

Attachments

  • Reward_Points_sample.xlsx
    11.3 KB · Views: 12
Hi again,

Sorry to bother you again, but this doesn't seem to really work. On another customer I have, I plugged this formula in and where their total should have been 263,984, It's only showing 213,097.

Is there any way to just figure out where the syntax is wrong in my current formula instead of rewriting it entirely?

Thank you so much for all the help you're giving me!!
 
Can you provide the sales values please?

Thanks,
 
Base: 70000
Jan: 11391
Feb: 5627
Mar: 7110
April: 15877
May: 8,392
June: 11,945
July: 22,266
Aug: 18,388

Thanks!
 
You know what, I've got it!

One small portion of your formula, it referred to $L$2...No other numbers had the $ in front, this was the only one so I missed it, so the other rows were actually referencing the wrong base quota.

I took that out and it works beautifully! So much for your help!!

I tested it on about 50 of my accounts to be sure and everything looks great!

I do have one more quick question for you if you don't mind...

I am completely, 100% self taught and I do feel that I do work extremely well with excel (this is the only complex formula I created that I had an issue with) - But your knowledge seems way more extensive. Did you take courses for this or are there any websites that you've used to learn? Any information would be great, I'd love to learn as much as I can!

Again, I REALLY apppreciate all your help, thank you so much for spending so much time on this for me. =^_^=
 
Exactly :) just saw that too. I'm an accountant by trade so I learned a little bit in classes. Basically I just learn by practice. Whenever I get stumped I try to research it and learn from it. I get a fair amount of complex issues with work, but I look at the forums here on occasion to try and stay sharp. I just try to solve as many issues as I can and add it to my repertoire. Excel is a huge puzzle, but usually learning one thing helps you to understand something else. The only advise I have is to keep doing new things to try and understand the big picture better.

Glad to help. You're very welcome,
 
Back
Top