IF Function with multiple functions

**C14389** · 05-17-2012, 05:43 PM

Hello,

I hoped someone could help me. I'm new to the forum and am grateful for the help it's given me already. I have half of a formula, but am unsure of how to complete it.

The scenario is that for our sales, people have to meet a minimum number. If that number is exceeded by 100-115% they get a 5% credit. I accomplished that with this formula.

=IF(AND(E5<1.15,E5>1),D5*0.05,0)

However, if the sales exceeds 115%, they still get the 5% credit, but only on the amount up to 115%. I'm not quite sure how to word that on Excel and have had a rough time getting to work. Would a MIN function be helpful? Any help would be appreciated.

Thanks

**vandan_tanna** · 05-17-2012, 06:35 PM

I find your explanation a little ambiguous. Can you give an example so we can better understand your request?

**C14389** · 05-21-2012, 10:28 AM

Sorry about that. Again my first time posting. Doing my best to explain it best I can.

What I'm looking to do is provide a certain amount of credits to some of our sellers for a certain amount sold. Each seller has a baseline amount they need to make each month. Let's say it's $100. So if they make 100%-115% of their baseline, they get 5% back. I've tried to reflect that here.

=IF(AND(E5<1.15,E5>1),D5*0.05,0)

However, if the seller goes over 115% of their baseline, we are only allowed to give them 5% up to that 115%. That's the issue I'm having to finish the formula. I was wondering if there's a way in the false column of the formula to make it so that if they don't meet the baseline they get no credit or give them their 5% up to 115% of what they sold.

I hope that this makes better sense.

**vandan_tanna** · 05-21-2012, 11:23 AM

I am still not clear about which amount to use for 5% credit: So I will give you two solutions:

Assuming Cell D5 has your baseline and E5 has actual sale:

#1:
You give 5% credit for exceeded mount: for example:

If baseline is $100, Sale is $114, you want to give 5% of $14 = 0.70 in credit
If baseline is $100, Sale is $130, you want to give 5% of $15 = 0.75 in credit

if So:

=IF(E5>D5, MIN((E5-D5)*5%, (15%*D5)*5%),0)

#2:

You give 5% credit of actual sale amount then

=IF(E5>D5, MIN(E5*5%,115%*D5*5%),0)

Hope this helps...

**C14389** · 05-21-2012, 12:21 PM

The second formula is the one I needed. Thanks for being patient. I better know how to ask questions on here.

But to answer your question,

C5 = Baseline
D5 = Sales Results
E5 = Percentage of Sales/Baseline.

Thanks again!

**C14389** · 05-22-2012, 05:16 PM

I tried your first formula and I'm getting an error in Excel which is too bad because I think this is exactly what I need to solve my problem.

Thanks.