•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How do you write an IF formula that changes the value based on which range, out of two ranges, the original value fell in?
Our take
Creating an IF formula in Excel to categorize values based on specified ranges can be straightforward with the right structure. In your case, you want to assign a value of 1 for numbers between 1 and 8, -1 for numbers between 9 and 21, and display FALSE for values outside these ranges. A suitable formula for your needs is:
`=IF(AND(A2>=1, A2<=8), 1, IF(AND(A2>=9, A2<=21), -1, FALSE))`.
This approach ensures that each condition is evaluated properly
I'm entering number values in Column A and I want Column B (where I'm inputting the formula) to populate either a 1 or a -1 based on which range (of two ranges) the value in Column A falls within.
The conditions are:
- If Column A value is between 1-8, then Column B will be 1.
- If Column A value is between 9-21, then Column B will be -1.
- If Column A value is outside the two ranges (below 1 or above 21), then Column B can/should say something like FALSE.
The formulas I've tried so far (and which failed) are:
- =IF(OR(AND(A2>=1,A2<=8),1)AND(A2>=9,A2<=21),-1)
- =IF(AND(A2<=1,A2>=8),1)OR(AND(A2>=9,A2<=21),-1))
Thank you in advance for all of your suggestions/help.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Tagged with
#Excel alternatives for data analysis#AI formula generation techniques#rows.com#cloud-based spreadsheet applications#formula generator#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#Excel alternatives#IF formula#Column A#Column B#1-8#9-21#number values#range#Excel#conditions#populate#value