Forum Discussion
Excel IF AND using range for criteria
HansVogelaar Thanks for the reply
=IF(OR(AND((A2-0.05)>$E$2,(A2+(B2/1000)+0.05)<$F$2),AND((A2-0.05)>$E$3,(A2+(B2/1000)+0.05)<$F$3),AND((A2-0.05)>$E$4,(A2+(B2/1000)+0.05)<$F$4)),"RED","NO PAINT")
Columns A is the start of the object on the field, Column B is the length of the object and E is the start of the RED paint and F is the end of the red paint. Hopefully this helps since i cant upload a photo. So Technically it isnt paint on a field but its a fairly simple analogy to make it all make sense without having to get to crazy with explanations. so, i have made the first part of the formula and there will be nested IF( functions so that i can add more options to what is returned. I will need AND in the formula since there are two criteria that must be followed in order to return a specific response. To make it a bit more complicated the Object from the first two columns has to be within the painted sections by at least 0.05m to be considered within those areas. Im trying to simplify the formula to take the entire array / table within columns E and F into the formula so that i dont have to use OR a zillion times for each row within columns E and F (and additionally for each row within H, I, K and L columns in the future). Hopefully you can understand what im trying to do from this screen shot.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
HansVogelaar I dont know how to add anything to this site. it seems quite limited.
Thank you for your private message. In Sheet2 in the attached version, I have created an alternative approach.
If you don't have Microsoft 365 or Office 2021, the formula must be confirmed with Ctrl+Shift+Enter, in the newer versions that isn't necessary.
- Thanks for the response. you're definitely on the right track compared to what i was doing. The only issue is that the tables for the different "Colours" are in separate tables on separate sheets. similar to how I had it on sheet 1