Conditional formatting for values in a column that exceed a set limitwhen summed

  • Thread starter Thread starter jaggy
  • Start date Start date
J

jaggy

Hi,

I'm looking for an approach that will let me sum up values in a column and then conditionally format the first value of the remaining values in a column that has exceeded a preset limit.

In other words, I have a list of tasks with estimates that I would like to compare against a limit to know how many tasks I can complete before I hit this limit. I would like to do this dynamically because the list will change periodically and I would like to know immediately when I have hit my limit.

Any help would be greatly appreciated.

-jaggy
 
Hi Jaggy,

Am Wed, 31 Dec 2014 13:51:42 -0800 (PST) schrieb jaggy:
I'm looking for an approach that will let me sum up values in a column and then conditionally format the first value of the remaining values in a column that has exceeded a preset limit.
Click to expand...

your values in column D, the linit in G1
Then select column D and use the following formula for the conditional
formatting:
=COUNTIF($D$1:D1,">"&$G$1)=1


Regards
Claus B.
 
Hi,

I'm looking for an approach that will let me sum up values in a column and then conditionally format the first value of the remaining values in a column that has exceeded a preset limit.

In other words, I have a list of tasks with estimates that I would like to compare against a limit to know how many tasks I can complete before I hit this limit. I would like to do this dynamically because the list will change periodically and I would like to know immediately when I have hit my limit.

Any help would be greatly appreciated.

-jaggy
Click to expand...

Hi Claus,

Thanks for the quick reply. I tried the formula provided but it gives a count of values in the column. I am looking for excel to sum all values in asingle column until it hits a predetermined total. I am looking to see how many tasks can be accomplished before we max out.

BTW - I tried SUMIF as well. No luck.

jaggy
 
Hi Jaggy,

Am Mon, 5 Jan 2015 14:12:03 -0800 (PST) schrieb jaggy:
I tried the formula provided but it gives a count of values in the column. I am looking for excel to sum all values in a single column until it hits a predetermined total. I am looking to see how many tasks can be accomplished before we max out.
Click to expand...

sorry. my bad.

Make a helper column and write in first row:
=SUM($D$1:D1)>=$G$1
and copy down. The first occurance of TRUE is greater than G1.
Or try it the opposite way.
Select D2:Dn and write in Conditional Formatting:
=D2+SUMME($D$1:D1)<$G$1
and select a format. Now the cells with sum < g1 are highlighted


Regards
Claus B.
 
Hi Jaggy,

Am Mon, 5 Jan 2015 14:12:03 -0800 (PST) schrieb jaggy:


sorry. my bad.

Make a helper column and write in first row:
=SUM($D$1:D1)>=$G$1
and copy down. The first occurance of TRUE is greater than G1.
Or try it the opposite way.
Select D2:Dn and write in Conditional Formatting:
=D2+SUMME($D$1:D1)<$G$1
and select a format. Now the cells with sum < g1 are highlighted


Regards
Claus B.
Click to expand...

Thanks Claus - that worked perfectly.
 
Back
Top