Formula Meaning In English

[Dropdown3]

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-

 

[Kevin B]

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,ReturnValueColumn,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

 

[Dropdown3]

The numbers will vary and will hardly if ever hit exactly on the target.
How does this affect the VLOOKUP and the number of reward points
assigned?

Lastly, I want to stick with the IF statement because I'm continuing
what someone else has started and I want to keep the format the same.

-Chuck-

 

[Kevin B]

If you drop the FALSE statement out of the VLOOKUP, which is optional, it
finds the closest match, not an exact match.

 

[Dropdown3]

Maybe someone will take a look at my first post and steer me in the
right direction with my IF statement.

Kevin, sometimes an employees data will hit the target, other times
they won't. If it doesn't hit the target, I need Excel to be able to
calculate just how many points they should receive. If a score of 10.00
yields 3.00 reward points, what if they score 7.75 points, or 9.00
points. How many reward points would they then receive. Hey man, I
really appreciate your posts, I'm about to pull my hair out

-Chuck-

 

[Kevin Vaughn]

=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)))))

The important point as far as your example is, If C6 is greater than C14
then Return the value that is in D14.
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

C6 is 594.49 which is greater than 491.31 therefore it is returning 10.

 

[Dropdown3]

Here is my current, incorrect formula:
=IF(Q5="","",IF(Q5>$Q$25,0,IF(Q5>=$Q$26,((Q5-$Q$25)/($Q$26-$Q$25))*($R$26-$R$25)+$R$25,IF(Q5<$Q$26,((Q5-$Q$26)/($Q$27-$Q$26))*($R$27-$R$26)+$R$26,0))))

Target................Points
Min......Q24 3.40 1.25 R24
Mid......Q25 3.06 5.00 R25
Max.....Q26 2.45 10.00 R26

As you can see, the lower the score the better. The most points you
SHOULD get would be 10.00 points. However, if I get a score of 2.44,
which is really good, I get 10.08 instead of 10.00. I want the cut-off
to be 10.00.

Should be If greater than or equal to R26 then receive 10.00 points.

Where am I going wrong?

-Chuck-

 

[Damon Longworth]

It is not clear to me what you are trying to accomplish, but this will limit
the answer to 10:

=IF(Q5="","",IF(Q5<$Q$26,10,IF(Q5>=$Q$26,((Q5-$Q$25)/($Q$26-$Q$25))*($R$26-$R$25)+$R$25,IF(Q5<$Q$26,((Q5-$Q$26)/($Q$27-$Q$26))*($R$27-$R$26)+$R$26,0))))


Here is an example IF construction:

=IF(Q5<Q26,"Less than q26",IF(Q5<Q25,"less than q25",IF(Q5<Q24,"less than
q24",0)))

--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21st, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, England
message
Here is my current, incorrect formula:
=IF(Q5="","",IF(Q5>$Q$25,0,IF(Q5>=$Q$26,((Q5-$Q$25)/($Q$26-$Q$25))*($R$26-$R$25)+$R$25,IF(Q5<$Q$26,((Q5-$Q$26)/($Q$27-$Q$26))*($R$27-$R$26)+$R$26,0))))

Target................Points
Min......Q24 3.40 1.25 R24
Mid......Q25 3.06 5.00 R25
Max.....Q26 2.45 10.00 R26

As you can see, the lower the score the better. The most points you
SHOULD get would be 10.00 points. However, if I get a score of 2.44,
which is really good, I get 10.08 instead of 10.00. I want the cut-off
to be 10.00.

Should be If greater than or equal to R26 then receive 10.00 points.

Where am I going wrong?

-Chuck-

 

[Dropdown3]

Damon:Your formula is the closest so far. However, it returns points if
the Target is 3.41 or higher, which it should return 0.00 points

Target.......................Points
Min......Q24...3.40.....1.25--- R24
Mid......Q25...3.06......5.00--- R25
Max.....Q26...2.45......10.00-- R26

Here is what I want from my point system:

If Q5 less than Q24 but greater than 3.06 you should get R24 value. If
Q5 is greater than Q24 you won't receive any points. If Q5 is greater
than Q25 but less than R25 you should get R25 value. If Q5 is less than
or equal to Q26 then R26.

If the employee falls in the score range of 3.40 or less, they should
receive a certain amount of points. If they ever score more than 3.40,
they will not receive any points.

The amount of points cannot be set to 10.00, 5.00, or 1.25 when a
certain score is entered into the cell Q5. I want the point output to
adjust to the number in cell Q5, but an employee cannot earn more than
10.00 points regardless of how low (good) a score because R26 is the
most amount of points they can earn.

-Chuck-

 

[Dropdown3]

I GOT IT!!
=IF(Q5="","",IF(Q5>$Q$25,0,IF(Q5<$Q$27,$R$27,IF(Q5>=$Q$26,((Q5-$Q$25)/($Q$26-$Q$25))*($R$26-$R$25)+$R$25,IF(Q5<$Q$26,((Q5-$Q$26)/($Q$27-$Q$26))*($R$27-$R$26)+$R$26,0)))))


Even though none of the replies hit exactly what I needed, they all
helped in getting me to my answer. Thanks.

-Chuck-

 

[Damon Longworth]

Try something similar to:

=IF(Q5<=Q26,R26,IF(Q5<=Q25,R25,IF(Q5<=Q24,R24,0)))

Adjust the TRUE portion to your desired calculation. It seems to work here
from my understanding of your requirements.

--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21st, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, England
message
Damon:Your formula is the closest so far. However, it returns points if
the Target is 3.41 or higher, which it should return 0.00 points

Target.......................Points
Min......Q24...3.40.....1.25--- R24
Mid......Q25...3.06......5.00--- R25
Max.....Q26...2.45......10.00-- R26

Here is what I want from my point system:

If Q5 less than Q24 but greater than 3.06 you should get R24 value. If
Q5 is greater than Q24 you won't receive any points. If Q5 is greater
than Q25 but less than R25 you should get R25 value. If Q5 is less than
or equal to Q26 then R26.

If the employee falls in the score range of 3.40 or less, they should
receive a certain amount of points. If they ever score more than 3.40,
they will not receive any points.

The amount of points cannot be set to 10.00, 5.00, or 1.25 when a
certain score is entered into the cell Q5. I want the point output to
adjust to the number in cell Q5, but an employee cannot earn more than
10.00 points regardless of how low (good) a score because R26 is the
most amount of points they can earn.

-Chuck-