Logical functions to calculate points

S

stellarblue11

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!
 
P

pdberger

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.
 
J

joeu2004

stellarblue11 said:
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
Click to expand...

If A1 contains the YOY%:

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

stellarblue11

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

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

David Biddulph

stellarblue11 said:
=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?
Click to expand...

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.
 
J

joeu2004

I wrote previously:
If A1 contains the YOY%:
=(A1>=2%)*2 + (A1>=5%)*3 + (A1>=10%)*5
Click to expand...
Will this also return the the desired point total for all the
possible percentage totals between the 2% and 4.99%,
for example?
Click to expand...

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.
 
Top