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!


--
stellarblue11

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.

"stellarblue11" wrote:


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!


--
stellarblue11

stellarblue11 wrote:
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

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?


--
stellarblue11

=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?


--
stellarblue11

"stellarblue11" wrote in
message om...

=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.
--
David Biddulph

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

stellarblue11 wrote:
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.