Format a cell that causes sum calculation to go over a pre-set number

[fireguy7]

I'm working with Loan amounts and i need to know how to set a excel spread sheet to conditionally format a cell that causes a sum formula to exceed a preset limit.

Example.


Said person has 5 loans.

1. $2000
2. $1000
3. $10000
4. $500
5. $2000

Total = $15,500

I want the spread sheet to Formate the cell that cuased the total amount to exceed $12,000

In this case the spreadsheet would auto formate the 3rd loan becuase it was the $10,000 loan that cuased the total amount to exceeded the $12,000 limit. Please help.

I'm on Excel 2003
Thanks.

[ChemistB]

Select the cells you are interested in checking, e.g. A1:A6 and go to conditional formatting and select "Formula is"

Please Login or Register  to view this content.

and select your formatting (e.g. orange background). as soon as the value would have exceeded 12,000, the cells will turn orange. Does this work for you?

[JBeaucaire]

Use conditional formatting. Let's say those loan values are in the range A2:A6...

1) Highlight the loan cells A2:A6
2) Click Format > Conditional Formatting
3) Click on Condition1 > Formula Is: =SUM($A$2:$A2)>12000
4) Click on Format > Pattern > Yellow
5) OK > OK

Now the loans will light up at the point they pass the 12,000 mark.

[seckert]

You'll actually need two rules for each cell. Using the same method mentioned in the two posts above, first check to make sure the cell above didn't trigger the highlight

Click on Condition1 > Formula Is: =SUM($A$1:$A1)<=12000 - normal format
Click on Condition2 > Formula Is: =SUM($A$1:$A2) > 12000 - highlighted

[fireguy7]

Thanks Everyone that's a big help.

Very close to what i need but not in full


I'm wanting the cell that caused the total amount to over to highlight and only the cell that caused it. With the above formula's it will highlight every cell once the rule is violated.


Basicly what i'm doing is checking accounts for people that have exceed credit limits and i need to know which specific purchase caused the excess. If i can get this to work i can just type in all the data and look for the highlighted cell.

Thanks again

[seckert]

My post above does what you need. The fomulas apply to cell $A$2. Condition one checks to see if the criteria has already been met. If so, it will have normal formatting. The second condition will highlight if needed.

[fireguy7]

Thanks Seckert that worked It still highlights every cell after the overage which isn't perfect but i can use the basic idea behid the formulas to figure out the rest. You've been a great help. Have a good one.

[JBeaucaire]

the solution ChemistB and I originally suggested also highlight the cell you want plus all the others that follow, so how is two CFs better than 1? Just wondering.

=========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[martindwilson]

how about format A1 as
=A1>=1200
A2 down

=AND(SUM($A$1:A2)>=1200,SUM($A$1:A1)<1200)

[fireguy7]

Thanks to everyone whom replied. It was a great help.