•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Trying to formulate cell with multiple IF Conditions based on range
Our take
Are you struggling to create a formula that categorizes values based on ranges in Excel? You’re not alone. Many users find themselves tangled in syntax when attempting to use multiple IF conditions, especially when defining ranges like "B4 >= 2 but < 7." The good news is that you can simplify your approach. In this guide, we’ll unlock the secrets to crafting a clean, effective formula that avoids the dreaded #ERROR! message. Let’s turn that complexity into clarity and get your spreadsheet working for you.
I'm trying to formulate a cell so that it outputs a value based on the number another cell has in it
C23 =IF(B4>2,"Negative") C23 =IF(B4>=2 but <7,"Neutral") C23 =IF(B4>=7 but <15,"Positive") C23 =IF(B4>=15,"Abnormal")
I feel like I'm really close to figuring out the syntax, but I'm struggling to figure out how to type out the range value with the (B4>=2 but <7) part
Alternatively I have tried doing it manually as a long formula and it keeps coming up as #ERROR!
=IF(B4=0,"Negative",IF(B4=1,"Negative",IF(B4=2,"Neutral",IF(B4=3,"Neutral",IF(B4=4,"Neutral",IF(B4=5,"Neutral",IF(B4=6,"Neutral",IF(B4=7,"Positive",IF(B4=8,"Positive",IF(B4=9,"Positive",IF(B4=10,"Positive",IF(B4=11,"Positive",IF(B4=12"Positive",IF(B4=13,"Positive",IF(B4=14,"Positive",IF(B4>=15,"Abnormal"))))))))))))))))
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How do you write an IF formula that changes the value based on which range, out of two ranges, the original value fell in?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. submitted by /u/LifeisSuperFun21 [link] [comments]
- Formula for letter gradesI'm trying to write a formula to put a letter grade in a cell based on the range of numbers in another cell. This is my first stab at it, but to be honest I barely understand how to do this. if((C23<10),"A")OR if(and(C24>9,C24<31),"B")OR if(and(C24>30,C24<61),"C")OR if(and(C24>60,C24<91),"D")OR if(C24>90, "F") submitted by /u/Future-Disastrous [link] [comments]
- Check whether a cell is between 2 different rangesI've got a form that we currently have two versions of, one for our 12 bit system and one for our 14 bit system. I'm trying to combine the two into one singular form. Part of our checks need us to validate whether our value falls within the acceptable range, but the range is different for the 12 and 14 bit system. Lets say the range is 3000-3500 for 12 bit, and 12000-13000 on 14. The current form has: IF(F20>3500, "FAIL",IF(F20<3000,"FAIL","PASS")) I'm not sure how to get the formula to check between two ranges. I suppose I could just put the limits on some backend page and have it very between the two with conditional formatting instead of a formula? submitted by /u/piezombi3 [link] [comments]
- IF/OR Formula with multiple criteria not working as desired (365 for enterprise - beginner)Hey all, I'm trying to make an IF/OR that will generate a "YES" even if only one of the listed criteria is met. Originally I was trying: =IF(OR($B2>6,$C2<80,$D2<3775, $E2="Y"),"NO","YES") Basically, if absences are greater than 6, Grade is lower than 80, STAAR score is less than 3775, OR Discipline = Y, then the output should be NO, the student is not eligible to exempt. However what I'm seeing is that nothing is happening unless I enter the STAAR score. Even at zero absences, the data does not change to YES. SO, I attempted a modified, more specific formula: =IF(OR($B2>6,$C2<80, $C2>0,$D2<3775, $D2>1649, $E2="Y"),"NO","YES") Even if I enter data that should result in a YES in the "Eligible?" column, it remains unchanged until I enter the STAAR score. Other note, I do have conditional formatting on columns B, C, D, and E, but only to change the cell color if certain data are present. For example C2>6 will turn red, C<6, C>2 will turn yellow, and C<3 will turn green. Please advise! Any help is appreciated. submitted by /u/The-Sweetest-Pea [link] [comments]
Tagged with
#rows.com#cloud-based spreadsheet applications#financial modeling with spreadsheets#AI formula generation techniques#Excel compatibility#Excel alternatives for data analysis#IF Conditions#cell formula#output value#range value#syntax#B4#Negative#Neutral#Positive#Abnormal##ERROR!#manual formula#comparison operators#output based on input