User profile for user: Bear34_1
Bear34_1 Author
User level: Level 1
12 points

Formula for Score Based on Value Range

I could use some help with a formula.


In the table Points System, I have a range with a score (points) for given numbers.


In the table Score, I would like to enter a value in column A and have the score for the value reflected in column B.


Is it possible to have a formula in column B of the Score table (e.g., if between 7 - 9 would equal a score of 4) that would not require the Points System table reference.


I saw a couple of BETWEEN functions, but neither were applicable. I tried several variations of multiple IF statements with no luck:


(IF(AND(A2≤19),0,IF(A2≤16,1,IF(A2≤13,2,IF(A2≤10,3,IF(A2≤10,3),IF(A2≤17,4),IF(A2≤17,3),IF(A2≤6,5)))))


Thank for your help.


Posted on Oct 6, 2024 5:19 PM

Reply
4 replies
Sort By: 
User profile for user: SGIII
SGIII
User level: Level 8
37,154 points

Oct 6, 2024 8:38 PM in response to Bear34_1

If you use the inexact match-type feature of XLOOKUP you can avoid getting tangled up in logic.



=XLOOKUP(A2,Points::A,Points::B,"out of range",1)


Note that the formula doesn't need a column for the lower end of the ranges. But you can of course add one for cosmetic reasons to remind yourself.


More on XLOOKUP here:


XLOOKUP - Apple Support


=XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)


SG


Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Formula for Score Based on Value Range

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up