How would you do this formula?

[Trisha V via OfficeKB.com]

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.

 

[Govind]

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]

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]

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]

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?

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]

 

[Govind]

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?

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.

 

[Trisha V via OfficeKB.com]

Thank you so much. That one worked great. Now I just need the % one and my
boss will be real happy...lol

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]