Highlighting a Weight Value That Is Below a Goal Weight

Solved

In the screenshot below (in my comment to this post), you can see where I've been manually highlighting the daily Pounds value that is less than the upcoming Goal value. For the life of me, I can't figure out the formula to automate this conditional formatting.

Posted Apr 27, 2023, 4:29 PM UTC · edited

39
ScottSEAOP•
1
aHorseSplashes28•

The simplest would be to put the goal weight in every cell in column C, e.g. 230 for 3/27 to 3/30, 229 for 4/2 to 4/7, etc. and then apply the conditional formatting custom formula =D2<C2 to the range D2:D.

If you want to maintain the current look of the table, you could add another conditional formatting custom formula =C2=C3 to the range C2:C in order to change the font color to white.

1
ScottSEAOP•

I don't want to put values in column C because I'm creating a "goal line" with those values in an associated chart:

​

https://preview.redd.it/8kcwbhm1uowa1.png?width=362&format=png&auto=webp&v=enabled&s=10d9518ac703aee6c963cd86bbaffd2f3785758b

1
aHorseSplashes28•

You could always use a different column for the chart, although it sounds like the point is moot in this case.

BTW, instead of "Accepted as sօlution", you should reply to awwsheets' post with Solution Vеrifiеd in order to award them a ClippyPoint. A bot assigns the points, so the wording needs to be exact.

1
[deleted]•

[deleted]

2
AutoModeratorMOD•

Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1
dynastyuserdude1•

Use conditional formatting, select D:D for the range and then under format cells if .... choose less than and enter the goal weight as the value and set the formatting options below.

1
awwsheets3•

If you're open to putting the goals in a separate table, you can do this: https://imgur.com/a/TtiWY8z

Then for the conditional formatting:

  • Apply to range: C2:C1000
  • Formula: =and(C2<>"",C2<=vlookup(min(filter($E:$E,$E:$E>=A2)),$E:$G,3,0))

And general tip for conditional formatting using custom formulas (which I also said in another comment, and demonstrated in the gray cells in the screenshot):

Imagine a second sheet on top of the sheet you're applying conditional formatting on. Imagine the top cell left of the "apply to range" to be the cell where you enter the formula, then the whole apply to range is where you would paste/autofill that formula to

Write the formula referencing the actual sheet, and any output that gives a "true" value would have the conditional formatting applied to it.

You can also check out this article (helped me a lot): https://www.ablebits.com/office-addins-blog/relative-absolute-cell-references-excel-conditional-formatting/

1
awwsheets3•

Alternate option for the original layout, if the goal is always decreasing you can do this: https://imgur.com/a/FMSGzoE

  • Apply to range: D2:D1000
  • Formula: =and(D2<>"",D2<=max($C2:$C))
1
ScottSEAOP•

Accepted as solution!

1
[deleted]•

[deleted]

1