| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula question
I need to do a formula as follows. If the amount in column A is < $1500 calculate it by .034. If the amount is $1500 then they get .00 of only the amount over %1500. Know how to do IF statements but can't seem to figure out how to calculate only on the amount under a certain value and not on the amount over that value. So results should be $1500*.034 = $51.00 00-1700 *.00 = $00.00 Total Commission $51.00 Help!!!! Thanks if you can do this.   -- Sharon D. ------------------------------------------------------------------------ Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
Sharon Try: =IF(A1<1500,A1*.034,1499*.034) if A1 is under $1500 it will multiply that value by .034 if A1 is over $1500 it will only multiply the first $1499 by .034, change $1499 to you max value. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
The formula by Pinmaster didn't work. Anyone else have an idea? -- Sharon D. ------------------------------------------------------------------------ Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
If I understand you corectly, commission is only calculated on the first $1500, since commision on $1500 is $51 you could put: =IF(A1<=1500,A1*.034,51) Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
The answer wouldn't always be $51.00. The answer would vary according to what the percentage is being calculated on. -- Sharon D. ------------------------------------------------------------------------ Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
=MIN(51,A1*.034)
-- Kind regards, Niek Otten "Sharon D." wrote in message ... I need to do a formula as follows. If the amount in column A is < $1500 calculate it by .034. If the amount is $1500 then they get .00 of only the amount over %1500. Know how to do IF statements but can't seem to figure out how to calculate only on the amount under a certain value and not on the amount over that value. So results should be $1500*.034 = $51.00 00-1700 *.00 = $00.00 Total Commission $51.00 Help!!!! Thanks if you can do this. -- Sharon D. ------------------------------------------------------------------------ Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
I know, the formula will only return 51 if A1 is over 1500, if under 1500 say 1125 it would return 38.25, isn't that what you want??? JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
I guess I haven't explained it very well. The answer will vary depending on what amount the formula is being calculated against. The answer will not always be $51.00. I need to have a formula that calculates commission on the first $1500 and not any additional money on anything over $1500. So if a person raises $1300. The percentage is .034 - the commission would be $44.20. If a person raises 1500. the percentage is still .034 the commission would be % $51.00. If the person raises 1600, the percentage is .034 for the first $1500 and the commission is still $51.00 because he doesn't get paid on the extra $100 over 1500. -- Sharon D. ------------------------------------------------------------------------ Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
Let me guess.....you haven't tried it?????? The formula will not always return 51, only when the amount is over 1500. -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
Sharon, the formula that Pinmaster gave you is EXACTLY what you need.
Anything 1500 or less gets multiplied by .034 and anything over 1500 shows only the maximum commission, which is 1500*.034. Just type it in and test it... trust me. "Sharon D." wrote: I guess I haven't explained it very well. The answer will vary depending on what amount the formula is being calculated against. The answer will not always be $51.00. I need to have a formula that calculates commission on the first $1500 and not any additional money on anything over $1500. So if a person raises $1300. The percentage is .034 - the commission would be $44.20. If a person raises 1500. the percentage is still .034 the commission would be % $51.00. If the person raises 1600, the percentage is .034 for the first $1500 and the commission is still $51.00 because he doesn't get paid on the extra $100 over 1500. -- Sharon D. ------------------------------------------------------------------------ Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
Why would she trust you when you were wrong concerning the MIN formula?
-- Regards, Peo Sjoblom "JR" wrote in message ... Sharon, the formula that Pinmaster gave you is EXACTLY what you need. Anything 1500 or less gets multiplied by .034 and anything over 1500 shows only the maximum commission, which is 1500*.034. Just type it in and test it... trust me. "Sharon D." wrote: I guess I haven't explained it very well. The answer will vary depending on what amount the formula is being calculated against. The answer will not always be $51.00. I need to have a formula that calculates commission on the first $1500 and not any additional money on anything over $1500. So if a person raises $1300. The percentage is .034 - the commission would be $44.20. If a person raises 1500. the percentage is still .034 the commission would be % $51.00. If the person raises 1600, the percentage is .034 for the first $1500 and the commission is still $51.00 because he doesn't get paid on the extra $100 over 1500. -- Sharon D. ------------------------------------------------------------------------ Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
As I stated before "Peo", I must have typed it in wrong when I tested it. My
appologies for being human. "Peo Sjoblom" wrote: Why would she trust you when you were wrong concerning the MIN formula? -- Regards, Peo Sjoblom "JR" wrote in message ... Sharon, the formula that Pinmaster gave you is EXACTLY what you need. Anything 1500 or less gets multiplied by .034 and anything over 1500 shows only the maximum commission, which is 1500*.034. Just type it in and test it... trust me. "Sharon D." wrote: I guess I haven't explained it very well. The answer will vary depending on what amount the formula is being calculated against. The answer will not always be $51.00. I need to have a formula that calculates commission on the first $1500 and not any additional money on anything over $1500. So if a person raises $1300. The percentage is .034 - the commission would be $44.20. If a person raises 1500. the percentage is still .034 the commission would be % $51.00. If the person raises 1600, the percentage is .034 for the first $1500 and the commission is still $51.00 because he doesn't get paid on the extra $100 over 1500. -- Sharon D. ------------------------------------------------------------------------ Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
It was a post on another tread by the OP, I suggested this formula to her =IF(A1<1500,A1*.034,51) but she didn't believe me so she started this tread. Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
Oops....she started this one first then she started a new one, same question and basically similar answers were given. JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
Thank you JG. I never thought about the fact that some people start
multiple threads. -- Regards Sandy with @tiscali.co.uk "pinmaster" wrote in message ... Oops....she started this one first then she started a new one, same question and basically similar answers were given. JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
"Sharon D." wrote:
The answer wouldn't always be $51.00. The answer would vary according to what the percentage is being calculated on. I assume your point is: 0.34 is not a constant. So change the best suggested formula to: =MIN(A1*A2, 1500*A2) where A2 is the commission rate (34% in your example). To make it even more flexible: =MIN(A1*A2, A3*A2) where A3 is the maximum amount that is subject to commission (1500 in your example). |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula question
Sandy: Yes, it was in another thread. I tested the MIN function and it
didn't work...apparently I typed it in wrong (my mistake). I was trying to get Sharon to just try one of the formulas because she kept saying they wouldn't work and we all knew they would. Then, "Peo" sends..."Why would she trust you when you were wrong concerning the MIN formula?" He/she is just FULL of the Christmas spirit, huh? "Sandy Mann" wrote: Thank you JG. I never thought about the fact that some people start multiple threads. -- Regards Sandy with @tiscali.co.uk "pinmaster" wrote in message ... Oops....she started this one first then she started a new one, same question and basically similar answers were given. JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495470 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Question | Excel Worksheet Functions | |||
| Formula question | Excel Discussion (Misc queries) | |||
| I have a question regarding countif formula. | Excel Worksheet Functions | |||
| An Excel Formula Question | Excel Worksheet Functions | |||
| Formula Question | Excel Discussion (Misc queries) | |||
Linear Mode
