Logiacl function with multiple conditions

stellarblue11 · 05-23-2006, 05:45 PM

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

**Dav** · 05-24-2006, 05:27 AM

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

stellarblue11 · 05-24-2006, 11:45 AM

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

**Dav** · 05-25-2006, 04:28 AM

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

As an if statement it is longer

if(A1>=10,"10 Pts",if(a1>=5,"5 pts", if(a1>=2,"2 pts"," ")))

Regards

Dav

stellarblue11 · 05-25-2006, 11:40 AM

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