Forum Discussion
Help with Data Validation of Min and Max with Overlap Yes/No
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!
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.
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
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.
