Help!! Number Formula?? Please

This Is What I Have To Do:

Say You Have A Cell With A Specific Value Cell A1 = (6) And This Cell Is Worth A Certain Number Of Points Depending On It's Value, And You Want The Point Value Input Into B1.....

So In This Case The Value Is 6 And It's Worth 2 Points.

Cell Value

<5 = 1pt.
5-6 = 2 Pt.
7-8 = 3 Pt.
9-10 = 4pt.
11-12 = 5pt.

And So On.....

So How Can I Create A Formula That Will Input The Pts. According To The Value Of A1 Into Cell B1??

Thank You

Good morning Karch35

I can't think of a standard Excel function that will do this quickly and cleanly, so I've written a function that should do the job OK for you. Copy this into an empty code module:

Function Points(a As Integer)
Application.Volatile True
If a < 5 Then Points = 1
If a >= 5 And a <= 6 Then Points = 2
If a >= 7 And a <= 8 Then Points = 3
If a >= 9 And a <= 10 Then Points = 4
If a >= 11 And a <= 12 Then Points = 5
End Function

Then the formula to use, quoting your example of 6 in A1 would be
=Points(A1)
and would return 2.

You don't say how far you want to take the number sequence - if you wanted to go as far as, say a hundred, it's going to be a pain continuing the code I've left you with, but once it's set up it's done.

HTH

DominicB

Thanks For The Reply.

Yes That Will Work For A Few Of Them Put Not All Of What I Need. Some Of The Value's Will Fall Between Say 300 And 350? So Is There Any Type Of Excel Formula That Says That If It's Between 2 Numbers This Would Be The Value??

Thanks

Hi karch35

There is no standard way to do this in Excel. You could use the nested =IF() function if you had less than 7 conditions - but you haven't. The function I wrote for you will do exactly what you asked for - you didn't list all the parameters, I worked on the ones you listed. If you want more conditions, then just add new lines before the End Function command:
If a >= 300 And a <= 350 Then Points = 100

You may have to spend a little time setting this up in the first place, but you have to balance this against how much time it will (or won't save). If the alternative is going through 000's of lines every week putting in these values manually or spending time listing all the conditions, I know which I'd prefer...

HTH

DominicB

Here are 3 alternatives:

=MATCH(B2,{0,5,7,9,11},1)
=VLOOKUP(B2,A12:B16,2,1)
=CHOOSE(1+(B2<5)*1+AND(B2>=5,B2<=6)*2+AND(B2>=7,B2<=8)*3+AND(B2>=9,B2<=10)*4+AND(B2>=11,B2<=12)*5,"Nothing",1,2,3,4,5)
...see enclosed

Hope it can be useful

Ola Sandström