Sep 18 2022 05:45 PM
Sep 18 2022 05:45 PM
Hello, I’m wondering if I can use a formula like this =IF(AND(Value1>any value in a range of cells,Value1<any values in another range of cells) I get the whole IF(AND(range of cells>value 1) but I’m trying to do it the other way where I have a value and need to see if it falls between any row in column a (start distance) and column b (end distance) column a and b might have many rows and the table im working with only has a few rows so it’s not like I can just drag a formula beside each one. An example would be if a field is covered from say 10m to 20m and again from 25m to 30m and possible a bunch more areas going from one end to another I need to find out if in my table the objects at a certain start distance and end distance fall between any of the areas that are covered on the field. Any help will be greatly appreciated!!!
Sep 19 2022 10:19 AM
@Hans Vogelaar Thanks for the reply
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.
Sep 19 2022 12:09 PM
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.
Sep 20 2022 02:43 AM
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.
Sep 20 2022 04:49 PM