Logical functions to calculate points

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stellarblue11 - 23 May 2006 20:32 GMT

I am trying to calculate points for a sales program based on YOY %
change with the following criteria:

2 – 4.99% - 2 pts
5 – 9.99% - 5 pts
10 +% - 10 pts


Any suggestions on how to format a logical function to effectively
represent the data?

Thanks in advance!

Signature

stellarblue11

Reply to this Message

pdberger - 24 May 2006 01:02 GMT

SB11 --

If your data is in cell A1, then:

=IF(A1<0.02,0,IF(A1<0.05,2,IF(A1<0.1,5,10)))

hth.

> I am trying to calculate points for a sales program based on YOY %
> change with the following criteria:
[quoted text clipped - 8 lines]
>
> Thanks in advance!

Reply to this Message

stellarblue11 - 24 May 2006 19:30 GMT

=IF(A10.02,0,IF(A10.05,2,IF(A10.1,5,10)))

Thank you for the help! I tried this one and either got a #NAME error
or a "too many functions for this argument" - which I have been running
into a lot. Any ideas?

Signature

stellarblue11

Reply to this Message

David Biddulph - 25 May 2006 11:23 GMT

> =IF(A10.02,0,IF(A10.05,2,IF(A10.1,5,10)))
>
> Thank you for the help! I tried this one and either got a #NAME error
> or a "too many functions for this argument" - which I have been running
> into a lot. Any ideas?

You misread what was said. The post to which you are replying said:
=IF(A1<0.02,0,IF(A1<0.05,2,IF(A1<0.1,5,10)))

For some reason you have replaced the less than symbols (<) with number 1.

Signature

David Biddulph

Reply to this Message

joeu2004@hotmail.com - 24 May 2006 01:52 GMT

> I am trying to calculate points for a sales program based
> on YOY % change with the following criteria:
> 2 - 4.99% - 2 pts
> 5 - 9.99% - 5 pts
> 10 +% - 10 pts

If A1 contains the YOY%:

=(A1>=2%)*2 + (A1>=5%)*3 + (A1>=10%)*5

Reply to this Message

stellarblue11 - 24 May 2006 19:25 GMT

Thank you for the suggesion! Will this also return the the desired point
total for all the possible percentage totals between the 2% and 4.99%,
for example?

Signature

stellarblue11

Reply to this Message

joeu2004@hotmail.com - 25 May 2006 16:05 GMT

I wrote previously:

> If A1 contains the YOY%:
> =(A1>=2%)*2 + (A1>=5%)*3 + (A1>=10%)*5

> Will this also return the the desired point total for all the
> possible percentage totals between the 2% and 4.99%,
> for example?

Yes. The formula will result in 0 if A1 is less than 2%, 2 if A1
is 2% or more but less than 5%, 5 if A1 is 5% or more but less
than 10%, and 10 if A1 is 10% or more. You can verify this
yourself by putting that formula into a cell, then putting various
values into A1 to be sure that it behaves as you want it to.

To understand the formula, note that "(A1>=2%)" is 0 when
A1 is less than 2%, and 1 when A1 is 2% or more. Likewise,
"(A1>=5%)" is 0 when A1 is less than 5%, and 1 when A1 is
5% or more. Thus, if A1 is between 2-5%, the formula is
effectively 1*2 + 0*3 + 0*5, resulting in 2. If A1 is between
5-10%, the formula is effectively 1*2 + 1*3 + 0*5, resulting in
5. If A1 is 10% or more, the formula is effectively 1*2 + 1*3 + 1*5,
resulting in 10.

Reply to this Message