Logiacl function with multiple conditions

S

stellarblue11

I am trying to write a formula to calculate points based on % change
YOY. The following are the award levels:

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

I aml ooking for the best way to write this into a logical statement.
Any suggestions would be greatly appreciated!
 
D

Dav

Create a lookup table

Eg
A B
1 0 '
2 2 2 pts
3 5 5 pts
4 10 10 pts


Include a space in Cell B1 otherwise it returns a 0

If the value you want to reference is in cell d1 use
=VLOOKUP(D1,$A$1:$B$4,2)

Regards

Da
 
S

stellarblue11

I understand your suggestion, but wouldn't I have to include a looku
for evey possible option? Wouldn't an IF or an IFAND statement be mor
appropriate? I would like to be able to just enter one formula to cove
all of the percent ranges and point possibilities.

Thanks in advance!
 
S

stellarblue11

I tried both and was unfortunately unsuccessful. I am totally confounded
by this seemily simple problem!

The lookup table, returned only the ', telling me that no matter what %
was referencing excel was just recognizing it as 0. The IF statement
returned 2 pts for a YOY % change of 146.3%, which was the same problem
I was running into initially.

Is excel perhaps referencing the value of the % change formula instead
of it's result?
 
Top