Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Baseline Formula
Excel 2007
I am trying to get a formula to do the following commision; If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that amount. If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus 5% of the amount over the $2,000.00 baseline. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Baseline Formula
=IF(A1<=2000,A1*2%,40+(A1-2000)*5%)
or (A1*2%)+(A12000)*(A1-2000)*5% best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "George" wrote in message ... Excel 2007 I am trying to get a formula to do the following commision; If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that amount. If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus 5% of the amount over the $2,000.00 baseline. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Baseline Formula
Something is wrong, when I am equal to or below $2,000.00 it's fine, but when
I go above $2,000.00 it shows a negative number. It should show the 2% plus the 5% above the $2,000.00 "Bernard Liengme" wrote: =IF(A1<=2000,A1*2%,40+(A1-2000)*5%) or (A1*2%)+(A12000)*(A1-2000)*5% best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "George" wrote in message ... Excel 2007 I am trying to get a formula to do the following commision; If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that amount. If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus 5% of the amount over the $2,000.00 baseline. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Baseline Formula
Wouldn't your second formula need to be either
=MIN(A3,2000)*2%+(A32000)*(A3-2000)*5% or =(A1*2%)+(A12000)*(A1-2000)*3% , Bernard? -- David Biddulph Bernard Liengme wrote: =IF(A1<=2000,A1*2%,40+(A1-2000)*5%) or (A1*2%)+(A12000)*(A1-2000)*5% best wishes "George" wrote in message ... Excel 2007 I am trying to get a formula to do the following commision; If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that amount. If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus 5% of the amount over the $2,000.00 baseline. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Baseline Formula
I was too hasty; yes we need
=A1*2%+(A12000)*(A1-2000)*3% thanks for the heads up! -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Wouldn't your second formula need to be either =MIN(A3,2000)*2%+(A32000)*(A3-2000)*5% or =(A1*2%)+(A12000)*(A1-2000)*3% , Bernard? -- David Biddulph Bernard Liengme wrote: =IF(A1<=2000,A1*2%,40+(A1-2000)*5%) or (A1*2%)+(A12000)*(A1-2000)*5% best wishes "George" wrote in message ... Excel 2007 I am trying to get a formula to do the following commision; If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that amount. If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus 5% of the amount over the $2,000.00 baseline. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Baseline Formula
Here's another formula that would work
=MIN(0.02*A1,40)+MAX(0.05*(A1-2000),0) "George" wrote: Excel 2007 I am trying to get a formula to do the following commision; If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that amount. If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus 5% of the amount over the $2,000.00 baseline. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Baseline Formula
=MIN(0.02*A1,40)+MAX(0.05*(A1-2000),0)
Another option... = Max(0.02*A1, 0.05*A1 - 60) = = = Dana DeLouis MrAcquire wrote: Here's another formula that would work =MIN(0.02*A1,40)+MAX(0.05*(A1-2000),0) "George" wrote: Excel 2007 I am trying to get a formula to do the following commision; If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that amount. If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus 5% of the amount over the $2,000.00 baseline. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a 98% baseline across my chart? | Charts and Charting in Excel | |||
Baseline values for comparison | Charts and Charting in Excel | |||
Baseline | Charts and Charting in Excel | |||
Can I add a baseline value to a chart? | Charts and Charting in Excel | |||
Adding a Baseline Value into my existing column graph. | Excel Worksheet Functions |