|
How would you do this formula?
how would I do a formula if the rules for the contest that Im working on read like this...If you sell $800 you get 2 points and each additional $100 you sell you get an additional $100 to a max of 10. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200508/1 |
Hi,
Use =IF(A1<800,0,MIN(10,2+(ROUND(A1-800,-2)/100))) where the sales are in cell A1. For the increase above 800, the increased is rounded off to the nearest 100. Regards Govind. Trisha V via OfficeKB.com wrote: how would I do a formula if the rules for the contest that Im working on read like this...If you sell $800 you get 2 points and each additional $100 you sell you get an additional $100 to a max of 10. |
IT KINDA WORKED. EXCEPT IT LOOKS LIKE EACH ADDITIONAL 100 IS GIVING ME 2 POINTS INSTEAD OF ONE. AND THE RULES READ THE FIRST $800=2 PTS. AND EACH ADDT'L $100=1. Govind wrote: Hi, Use =IF(A1<800,0,MIN(10,2+(ROUND(A1-800,-2)/100))) where the sales are in cell A1. For the increase above 800, the increased is rounded off to the nearest 100. Regards Govind. how would I do a formula if the rules for the contest that Im working on read like this...If you sell $800 you get 2 points and each additional $100 you sell you get an additional $100 to a max of 10. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200508/1 |
Hi,
For each additional above 800, it will give only one point. However, note that above 800, it rounds off the variance to nearest 100. For eg. if the sales is 960, it might give 4 points (2 for 800, and 2 (960 less 800 rounded to nearest 100) for above 800). Let me know how you want that to be rounded off and i can modify the formula accordingly. Regards Govind. Trisha V via OfficeKB.com wrote: IT KINDA WORKED. EXCEPT IT LOOKS LIKE EACH ADDITIONAL 100 IS GIVING ME 2 POINTS INSTEAD OF ONE. AND THE RULES READ THE FIRST $800=2 PTS. AND EACH ADDT'L $100=1. Govind wrote: Hi, Use =IF(A1<800,0,MIN(10,2+(ROUND(A1-800,-2)/100))) where the sales are in cell A1. For the increase above 800, the increased is rounded off to the nearest 100. Regards Govind. how would I do a formula if the rules for the contest that Im working on read like this...If you sell $800 you get 2 points and each additional $100 you sell you get an additional $100 to a max of 10. |
I understand..in fact I checked with 950 and it gave me 4 points. Is there that it wont do that since I need those numbers to be exact? Govind wrote: Hi, For each additional above 800, it will give only one point. However, note that above 800, it rounds off the variance to nearest 100. For eg. if the sales is 960, it might give 4 points (2 for 800, and 2 (960 less 800 rounded to nearest 100) for above 800). Let me know how you want that to be rounded off and i can modify the formula accordingly. Regards Govind. IT KINDA WORKED. EXCEPT IT LOOKS LIKE EACH ADDITIONAL 100 IS GIVING ME 2 POINTS INSTEAD OF ONE. AND THE RULES READ THE FIRST $800=2 PTS. AND EACH [quoted text clipped - 17 lines] like this...If you sell $800 you get 2 points and each additional $100 you sell you get an additional $100 to a max of 10. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200508/1 |
Hi,
If you dont want any rounding off at all, use =IF(A1<800,0,MIN(10,2+((A1-800)/100))) But, say the sales are 950, this would give 3.5 points. If this is how you want the points to be given, use this. On the contrary, if you want only completed 100's to be given points, use =IF(A1<800,0,MIN(10,2+(ROUNDDOWN(A1-800,-2)/100))) In this case 950 will be given only 3 points, as only one 100 is complete after 800. Govind. Trisha V via OfficeKB.com wrote: I understand..in fact I checked with 950 and it gave me 4 points. Is there that it wont do that since I need those numbers to be exact? Govind wrote: Hi, For each additional above 800, it will give only one point. However, note that above 800, it rounds off the variance to nearest 100. For eg. if the sales is 960, it might give 4 points (2 for 800, and 2 (960 less 800 rounded to nearest 100) for above 800). Let me know how you want that to be rounded off and i can modify the formula accordingly. Regards Govind. IT KINDA WORKED. EXCEPT IT LOOKS LIKE EACH ADDITIONAL 100 IS GIVING ME 2 POINTS INSTEAD OF ONE. AND THE RULES READ THE FIRST $800=2 PTS. AND EACH [quoted text clipped - 17 lines] like this...If you sell $800 you get 2 points and each additional $100 you sell you get an additional $100 to a max of 10. |
Thank you so much. That one worked great. Now I just need the % one and my boss will be real happy...lol Govind wrote: Hi, If you dont want any rounding off at all, use =IF(A1<800,0,MIN(10,2+((A1-800)/100))) But, say the sales are 950, this would give 3.5 points. If this is how you want the points to be given, use this. On the contrary, if you want only completed 100's to be given points, use =IF(A1<800,0,MIN(10,2+(ROUNDDOWN(A1-800,-2)/100))) In this case 950 will be given only 3 points, as only one 100 is complete after 800. Govind. I understand..in fact I checked with 950 and it gave me 4 points. Is there that it wont do that since I need those numbers to be exact? [quoted text clipped - 20 lines] like this...If you sell $800 you get 2 points and each additional $100 you sell you get an additional $100 to a max of 10. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200508/1 |
| All times are GMT +1. The time now is 10:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com