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