Creating a "Point System" Spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi There,

I am trying to create a spreadsheet where if a certain cell has a number
that falls into a certain range, it would be assigned a certain number/point.

For example: If a rate is 7.5%-8%, 2 pts will be assigned; if 8%-8.5%, then
3 points will be assigned and so on.

How do I do this?

Thank you,
MT
 
Set up a table somewhere (eg in X1:Y10) which will be made up of the
start value of your range and the number of points you want to award,
eg:

0% 0
5% 1
7.5% 2
8% 3
8.5% 4

etc., then with a percentage in A1 you can use this formula to get the
points:

=VLOOKUP(A1,X$1:Y$10,2)

Copy down if required.

Hope this helps.

Pete
 
One option might be =IF(A3>=7.5%,2+INT((A3-7.5%)/0.5%),"")
It is a bit inconsistent as to how it treats values which lie right on the
border, as presumably some values fall victim to rounding errors in fixed
point binary. If need be, a ROUND function could presumably tidy the
situation up.
 
Back
Top