Mac Numbers version 5.1 (Yosemite)
I just need a simple formula that will highlight a cell based on the difference between two other cells.
Cell N9 would “fill” if P17>J17. I would adapt the formula to fit the other cells in column N.
Example: N1 would fill if P17-J17>=2 N2 would fill if P17-J17>=3 etc. (I guess P17-J17>=1 works for Cell N9)
I am new to formulas and don’t know the language…yet.
Any help would be appreciated.
Thanks!
—–
KingB84
You can't highlight a cell based on the value of other cells. You can highlight a cell based on its own value. That is called conditional highlighting.
But if you want to indicate that some condition has been met, then use another cell in the row. For instance, you could have values in B2 and C2, and then a formula in D2 to see if one is greater than the other. It would then have a true or false value in D2. You could make that a checkbox or just regular text, and use conditional highlighting to color it in if it is true.
But I'm not sure what would work for you since you want the value of cells in row 2 to change based on the values in row 17. Did you have values in N1 and N2? If so, maybe move those over and put checkboxes next to them with your formulas to check the row 17 values. Hard to say without knowing your design.
Strange, but I just watched another video you posted. "Conditional highlighting based on other cells". You were able to "fill" a third cell based on the other two cells. That's all I'm trying to do. You have "reorder" appear in the fill cell. I would have 1 or 2 or 3 or 4 etc. appear in the cell based on whether P17-J17 was = to 1 or 2 or 3 etc. It's a scorecard in golf where the differences in HC's will highlight the stroke holes for match play. I was able to have a 1 appear, not the fill.
KingB84: To get the fill, you need to use conditional highlighting, like I show in that video. But it is based on the value of that cell. So if you just want the cell highlight to change based on the value in that same cell.
This formula works for 1. IF(P17>J17,"1","") (I chose to fill if that cell "is 1") It works fine.
I need one that will work for P17-J17>=2 & I'll adapt it to 3,4,5,6,7,8,9 etc.
I'll use conditional highlighting to fill each cell if it meets the requirements. I've got that working.
I just don't know how to write the "proper" formula.
KingB84: Is it just that you want something to replace the 1 so it is 2, 3, 4, etc in each cell? Use COLUMN() for that. COLUMN() gives you the number of the column. The you need to make the referenced cells absolute. So IF($P$17-$J$17>COLUMN(),"1","")
You can paste that into all of your cells and the COLUMN() value will be the column number.
Gary, I believe that is what I need. I can adapt it for the # in each cell. I tried it on the side and it seemed to work. I'll give it a more thorough look tomorrow. I'll check back with you then.
Thanks
KingB84
Didn't work. Probably me. This is what I tried.
=if($P$17-$J$17>=Column(N8),"2","")
This is what worked.
=if($p$17-$j$17>=2,"2","") 2 was the value in cell N8.
As I said, probably something I did wrong but I adapted your formula for me and it worked.
Thanks again!
KingB84: It is COLUMN() with nothing in the parenthesis. If you use COLUMN(N8) you will always get 14 for the value as Column N is column 14.