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