• 100% Satisfaction Guarantee

Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 32164
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now

# I need to create a formula based on the grid below. HELP!!

I need to create a formula based on the grid below. HELP!!

LEASE PEN 'C'
15% > 20% > 25%
SAS MECH PEN MECH CPO MECH CPO MECH CPO MECH CPO
30% \$50 \$50 \$50 \$50
40% \$75 \$75 \$75 \$75
45% \$100 \$50 \$100 \$50
50% \$100 \$50 \$100 \$50 \$125 \$60
55% \$125 \$60 \$125 \$60
60% \$125 \$60
Thank you for your question, my name is Richard.

Can you tell me please what you would like the formula to do?

Thank you
Customer: replied 3 years ago.

There are multiple variables. I can send you the grid if that makes it easier.

I am trying to figure out projected bonuses based on various penetration levels.

Before you send it Tom,

Tell me in your own words (not formulas) what you want to accomplish please.

Customer: replied 3 years ago.

OK.

If product penetration is >30% and lease penetration is <15% then they earn \$50 per contract. If product penetration is >40% and lease penetration is <15% then they earn \$75 per contract. If product penetration is >50% and lease penetration is <15% then they get paid \$100 per contract plus \$50 per CPO. If product penetration is >60% and lease is <15% they get paid \$125 per contract plus \$60 per CPO.

unless

If product penetration is >30% and lease penetration is >15% then they earn \$50 per contract. If product penetration is >40% and lease penetration is >15% then they earn \$75 per contract. If product penetration is >50% and lease penetration is >15% then they get paid \$100 per contract plus \$50 per CPO. If product penetration is >55% and lease is >15% they get paid \$125 per contract plus \$60 per CPO.

unless

If product penetration is >30% and lease penetration is >20% then they earn \$50 per contract. If product penetration is >40% and lease penetration is >20% then they earn \$75 per contract. If product penetration is >45% and lease penetration is >20% then they get paid \$100 per contract plus \$50 per CPO. If product penetration is >55% and lease is >20% they get paid \$125 per contract plus \$60 per CPO.

unless

If product penetration is >30% and lease penetration is >25% then they earn \$50 per contract. If product penetration is >40% and lease penetration is >25% then they earn \$75 per contract. If product penetration is >45% and lease penetration is >25% then they get paid \$100 per contract plus \$50 per CPO. If product penetration is >50% and lease is >25% they get paid \$125 per contract plus \$60 per CPO.

Thanks for that Tom.

Can I have the sheet now please?

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thank you
Customer: replied 3 years ago.

578922

Thanks! Product penetration refers to Mechanical penetration.

And the Formula goes in J correct?
Customer: replied 3 years ago.

Yes. That would be perfect.

This is done now Tom

The File ID is 187452

Thank you

Customer: replied 3 years ago.

It doesn't look right. The first one should be \$375, second should be \$6055.

But you say (and this is the maximum)

they get paid \$125 per contract plus \$60 per CPO.

So the max can only be 185?

Or should this be multiplied by something?

Customer: replied 3 years ago.

Should be multiplied. The \$125 should be multiplied by the "Mech Sales" count in column C, and the \$60 should be multiplied by the "CPO Sales" count in column D.

ok, this is done and you can download it under File ID 715290

Let me know if you have any difficulties please

Thank you
Customer: replied 3 years ago.

still not adding up. Take a look at the dealer in row 5, Pine Belt. They had 47 contracts * \$125 plus 3 CPO sales *\$60. Should add up to \$6055.

This is the problem I couldn't figure out. I was getting the same thing.

It calculates correct.

It is greater then 30 and greater then 15 so 50 x 47 = 2350

On that equation you did not specify anything times the CPO Sales.

If product penetration is >30% and lease penetration is >15% then they earn \$50 per contract.

But I think you gave me wrong information

It is not just Mechanical penetration. but also Leases in H correct?
Customer: replied 3 years ago.

No. The number of leases in H was just a way for me to figure the lease percentage. You are correct, but the were 63% mechanical penetration which would pay them \$125 per mechanical contract, plus 27% lease penetration which would pay \$60 per CPO.

Confusing, yes?

yes, as you are not telling me the formula correct Tom, thats only making it confusing, and I am working hard on what you are telling me.

This is what you said

If product penetration is >30% and lease penetration is >15% then they earn \$50 per contract.

There is no mention of \$60 per CPO, or is that for all the matches in that block?
Customer: replied 3 years ago.

Let me try this.

If lease penetration is <15% then they are paid this way

1. Mechanical penetration is >30% but <40% then \$50 per mechanical contract

2. Mech Pen is >40% but <50% then \$75 per mech contract

3. Mech pen is >50% but <60% then \$100 per mech contract + \$50 per CPO

4. Mech Pen is >60% then \$125 per mech contract +\$60 per CPO

If lease pen is >15% but <20% then they are paid this way

1. Mechanical penetration is >30% but <40% then \$50 per mechanical contract

2. Mech Pen is >40% but <50% then \$75 per mech contract

3. Mech pen is >50% but <55% then \$100 per mech contract + \$50 per CPO

4. Mech Pen is >55% then \$125 per mech contract +\$60 per CPO

If lease pen is >20% but <25% then they are paid this way

1. Mechanical penetration is >30% but <40% then \$50 per mechanical contract

2. Mech Pen is >40% but <45% then \$75 per mech contract

3. Mech pen is >45% but <55% then \$100 per mech contract + \$50 per CPO

4. Mech Pen is >55% then \$125 per mech contract +\$60 per CPO

If lease pen is >25% then they are paid this way

1. Mechanical penetration is >30% but <40% then \$50 per mechanical contract

2. Mech Pen is >40% but <45% then \$75 per mech contract

3. Mech pen is >45% but <50% then \$100 per mech contract + \$50 per CPO

4. Mech Pen is >50% then \$125 per mech contract +\$60 per CPO

Make better sense?

ok, can you check this please?

294130

Let me know the results please

Customer: replied 3 years ago.

That looks good! Thank you! Sorry about the confusion.

Its ok! and it was a tough one :)

But it should be correct.

If you have continued problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you