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!


--
stellarblue11

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

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=544891

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

Thanks in advance!!


--
stellarblue11

I dont think so, unless I misunderstood what you want. Try it!

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=544891

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?


--
stellarblue11