Help with Data Validation of Min and Max with Overlap Yes/No

Copper Contributor

Hello,

 

I have difficulty creating a data validation set. How do I create one column that automatically answers "yes" or "no" (I am assuming something similar to True/False) by itself using the values from the previous 2 columns with a min/max? I have attached an image below to follow with and explain more thoroughly. 

 

Looking at the image:

I need Column P to answer Yes/No using the values from Columns N and O for each row with a value.

 

In each row, Column N values need a minimum of 400, and Column M values need a maximum of 500. (This overlap data validation answer would be NO in Column P). If one or both of the Columns N and O value do NOT succeed the min/max, the overlap data validation answer would be YES in Column P)

 

(#1)For example, in row 8, N8 is 8700 and O8 is 8900. N8 succeeds the 400 minimum, but O8 oversees the 500 maximum. Therefore, the overlap data validation answer for this is YES because even though N8 reached the minimum, O8 went beyond the maximum. 

 

(#2) Hypothetical example: Say if N1 value is 435 and O1 value is 450. This overlap data validation answer will be NO. This is because N1 meets the 400 minimum and O1 also meets the 500 maximum. So there is no overlapping between these values. 

 

The answer will be....

YES: If Column N and/or Column O values do not succeed the 400 minimum and 500 maximum. 

NO: If BOTH Columns N and O, succeed the 400 minimum and 500 maximum. 

 

Thank you SO much! Let me know if there are any more details I could give for assistance! I really need help on this assignment ASAP! 

 

 

10 Replies

@anniehoang Perhaps best demonstrated in the attached example. Adjust the references to your own.

 

@anniehoang 

 

Since you've already gone to the trouble of creating the spreadsheet, unless it has proprietary information in it, it would be a LOT easier to help you if you attached the spreadsheet itself. I'm going to suggest a formula that should work, but with the actual, we could test it.

 

IF(AND(N8>$N$4,O8<$O$4),"Yes","No")

Copy that down to succeeding rows. You can surround it with other conditions for blank rows....

 

For future reference, though, when you ask for help, you help us help you by posting the sheet itself, not just an image.

@mathetes 

 

Hello, I understand, I was only unable to upload the spreadsheet and share because it has proprietary information in it. Unfornately, I am not sure if a formula to copy for each row would work. This spreadsheet has over 10,000 rows and I would need a format tool instead for it to be time fast and efficient instead of time consuming. Unless you know how to copy the formula from top to bottom of the spreadsheet in a quick manner that'd be appreciated!

 

I was looking at the "Data Validation" to see what information I could put in to have it spit out Yes/No. Again, I am not sure.

 

Thank you!

@anniehoang 

 

It's very easy to copy something from the top to the bottom of a column. The commands vary between Mac and PC.  Put an x or any other character in the bottom cell. Then from the top, Ctrl+C to copy, Shift+Ctrl+down arrow to highlight all the rows to that bottom, then Ctrl+V to paste. With the Mac, substitute the Command key for the Ctrl key.

 

 

@anniehoang

 

Data Validation is something altogether different. You're looking in this case for a formula to check on the content of two other cells. You've been given,  by @Riny_van_Eekelen  and me, two different (equally functional) formulas.

 

Data Validation could be used, if this were applicable, though I don't think it is, if you were entering the frequencies for a given station and only wanted to accept entries that met certain criteria. Or it can be used to make sure a name of say a department is on the approved list, thus not only validating that it's on the list but also that it gets entered with the correct spelling, etc. Anyway, different purposes than what you were wanting to do here.

@mathetes I understand! Thank you for the clarification!

@mathetes This formula worked! Thank you! One last issue, Is there a way I could remove the random "Yes" or have the cell say "No" to the cells which are blank and do not have frequency values in Columns N and O.  I have attached a snapshot to walk through. 

 

For example, the blanks cells N10, N11, etc., and O10, O11, etc. do not have values. So it does not make sense for the blank cells to say Yes.  What could I put in the formula to have the blank cells in columns N and O to say "No" instead of Yes or keep those cells also blank.

 

Thank you

@anniehoang 

 

Well, first off, if we were meeting face to face, I'd ask "Why are there blank rows to begin with?" In general, people do that for the purpose of clarity, but--as you're experiencing--it complicates things when you're working with data. It's far better to design your "raw data" as a table, which means contiguous rows and columns of data, especially in the rows. Having an occasional blank cell in a column is a function of the data itself, but empty rows are a waste of space.

 

Use the visual spacing at the output end of things, for reports.

 

Now, having said that with regard to spreadsheet design, if you still want to retain empty rows, you can nest the formula you have in yet another IF statement, something like this:

=IF(AND(A8="",B8=""),"",IF(AND(A8>=$A$4,B8>=$B$4),"Yes","No"))

 

This will leave the field blank if both columns A and B are blank. More sophisticated versions can be created, but this works.

@mathetes Hello, I understand. I did not make this spreadsheet, I have been only asked to change the formatting.

 

I have another last issue. My apologies, I read the assignment wrong for the yes/no formula in column P.

 

It should be...:

YES: If ONE OR BOTH of the values from columns N and O contain a value between the range 400-500.

NO: If NONE of the values from columns N and O contain a value between the range 400-500.

 

I have attached another snapshot to explain through:

For example:

In row 8, 8700 (N8) and 8900 (O8), NEITHER/NONE of these numbers are between 400-500, so this is a "NO" in Column P.

 

In row 12, 225 (N12) and 400 (O12), ONE of these numbers (400) are between 400-500, even though 225 is not, ONE of the values is. so this is a "YES" in Column P.

 

In row 35, 406 (N35) and 420 (O35), BOTH of these numbers (406 and 420) are between 400-500. so this is "YES" in Column P.

 

Please help me formulate a Yes/No in column P formula using values from columns N and O for each row! Thank you again for your help.

 

@anniehoang 

 

You're doing more than changing "formatting" here, it seems to me.

 

So I've attached another example, building on the one that  @Riny_van_Eekelen gave you earlier. It's up to you to enter the appropriate cell references to accomplish what you want.

 

These are tricky, figuring out all the IF...THEN conditions, but I would challenge you to take the time. All of us have learned "the hard way" ourselves. Granted, it comes more easily to some than to others, but one of the best ways to learn how this works is to struggle through. It helps, too, to have a good reference  I did my learning back in the days when software actually came with thick user manuals...Lotus 1-2-3, the first really mainstream spreadsheet, had multiple printed manuals with extensive documentation on how to do things like this. I used to read them cover to cover. Today, there are good websites to plow through. Here's a good one, with the link taking you straight to the description of the IFS function that's at the heart of the solution I've given you. https://exceljet.net/excel-functions/excel-ifs-function