You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
User profile for user: SGIII
SGIII
User level: Level 8
36,134 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

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