You could use a VLOOKUP function to locate an Excact match instead of a
multi-conditional IF statement.

The VLOOKUP formula has the following syntax:

VLOOKUP(ValueToLookUp,LookUpTableRange,ReturnValue Column,FALSE)

The FALSE statement at the end of the lookup indicates an exact match only.

As an example, if your value are in Column A row 1 and the macth in in
Column B Row1, and the lookup table that holds the results is on Sheet2,
cells A1 through B3, the formula would be the vlookup below:

A B
4.50 VLOOKUP(A1,Sheet2!$A$1:$B$3,2,FALSE)
4.75
5

The lookup Table would look like this:

A B
4.50 5.0
4.75 20.00
5 40.00

--
Kevin Backmann


"Dropdown3" wrote:


I'm trying to develop a point system here at work to give a certain
amount of points for receiving a certain grade. I'm not that well
versed in Excel so I don't know what these formulas mean in English.

=IF(C6="","",IF(C6<$C$12,0,IF(C6$C$14,$D$14,IF(C6 <=$C$13,((C6-$C$12)/($C$13-$C$12))*($D$13-$D$12)+$D$12,IF(C6$C$13,((C6-$C$13)/($C$14-$C$13))*($D$14-$D$13)+$D$13,0)))))

Target Points
C12 594.49 1.25 D12
C13 540.44 5.00 D13
C14 491.31 10.00 D14

With the formula above, when I enter 594.49 into cell C6 I'm getting
10.00 pts when I should be getting 1.25 points.


I created this one and it works exactly as I intended, but I can't just
copy and paste. I will need to create about 10 of these little point
matrix's and all of them have different targets and different reward
points.

=IF(G6="","",IF(G6<$G$12,0,IF(G6$G$14,$H$14,IF(G6 <=$G$13,((G6-$G$12)/($G$13-$G$12))*($H$13-$H$12)+$H$12,IF(G6$G$13,((G6-$G$13)/($G$14-$G$13))*($H$14-$H$13)+$H$13,0)))))

Target Points
G12 4.50 5.00 H12
G13 4.75 20.00 H13
G14 5.00 40.00 H14

Any help would be greatly appreciated.

-Chuck-


--
Dropdown3
------------------------------------------------------------------------
Dropdown3's Profile: http://www.excelforum.com/member.php...o&userid=30321
View this thread: http://www.excelforum.com/showthread...hreadid=505440