Highlight rows till the sum is close to X Value

[asolopreneur]

 

[Joe4]

You need to clearly define what qualifies as "close to"?
Exactly how close does it need to be?

 

[Fluff]

Is this for Excel, or Google Sheets?

 

[asolopreneur]

You need to clearly define what qualifies as "close to"?
Exactly how close does it need to be?

minimmum

 

[asolopreneur]

Is this for Excel, or Google Sheets?

google sheets

 

[Fluff]

google sheets

In future please clearly state that in the title, or the post. Thanks

 

[asolopreneur]

You need to clearly define what qualifies as "close to"?
Exactly how close does it need to be?

In this case 510 (which is G18) would be okay

 

[Joe4]

minimmum

I can only assume that you mean the "highest value that is still less than or equal to the value you are looking for", i.e. 510 is still less than 535, but 540 is not (hence the value that adds up to 510 is the last value you want).

However, I do not use Google Sheets at all, so I have no idea if Conditional Formatting works the same in Google Sheets as it does in Excel.
If it does work the same, you would just select cells F2 down to the end of the data in column F, and enter the following Conditional Formatting formula:

=SUM(F$2:F2)<=G$2

and select your yellow fill option.

If it does not work the same way in Google Sheet, I will need to "punt" on this one...

 

[asolopreneur]

Perfect!

Can you please explain why G$2 and not G2 or $G2 or $G$2? (Just to improve my Excel sheet skill)

 

[Joe4]

Using a "$" in front of the column or row reference makes it an "absolute" reference, which "locks it down", so that it doesn't float as you move down the column.

So, since our first formula is applied to cell F2, then F3 (and all other cells in column F) would then also look at G2.
If we did not have the "$" there, F3 would look at G3 instead of G2.

See here for more details: Switch between relative, absolute, and mixed references