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.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hi there
I would like to know what formula are you going to use if you want an empty cell to display a text corresponding to a range of numbers? Say you want cell to say the word 'Critical' for the numbers 14-16 or 'High' for numbers 10-13 placed in another cell. Many thanks.
MacBook Air 13″, macOS 12.3
Micol216 wrote:
display a text corresponding to a range of numbers
You can use the XLOOKUP function with its 'exact or next largest' parameter like this:
In this example I've entered this in I3, and filled or copied it down the column:
=XLOOKUP(H3,{10;13;16},{"Moderate";"High";"Critical"},"",1)
If your region uses , as a decimal separator then you would replace the , with ; and your formula would thus become:
=XLOOKUP(H3;{10;13;16};{"Moderate";"High";"Critical"};"";1)
I assumed the numerical value was in column H but you can change the formula to refer to whatever column contains the values you want to associate with text.
The series between the first { } contains the "ranges" - less than 10, 10-13, 13-16 and the second series between the { } contains the text you want to correspond to each of the ranges. You can easily change these values to correspond with the ranges you want. The number of values between the first { } needs to be the same as the number of values between the second { }.
You can also put the ranges in columns of cells and refer to those columns in the formula. But including them in the formula is easy and compact.
A full explanation of XLOOKUP, with examples, can be found here. This can be accessed via Help > Formulas and Functions Help in your menu.
SG
Micol216 wrote:
display a text corresponding to a range of numbers
You can use the XLOOKUP function with its 'exact or next largest' parameter like this:
In this example I've entered this in I3, and filled or copied it down the column:
=XLOOKUP(H3,{10;13;16},{"Moderate";"High";"Critical"},"",1)
If your region uses , as a decimal separator then you would replace the , with ; and your formula would thus become:
=XLOOKUP(H3;{10;13;16};{"Moderate";"High";"Critical"};"";1)
I assumed the numerical value was in column H but you can change the formula to refer to whatever column contains the values you want to associate with text.
The series between the first { } contains the "ranges" - less than 10, 10-13, 13-16 and the second series between the { } contains the text you want to correspond to each of the ranges. You can easily change these values to correspond with the ranges you want. The number of values between the first { } needs to be the same as the number of values between the second { }.
You can also put the ranges in columns of cells and refer to those columns in the formula. But including them in the formula is easy and compact.
A full explanation of XLOOKUP, with examples, can be found here. This can be accessed via Help > Formulas and Functions Help in your menu.
SG
Works like a charm! You saved me. Thank you so much!
What formula to use in Numbers if I want to assign a value for a range of values?
