I have a simple check balancing sheet. I figured out how to format a cell to change colors if the balance in another cell (total) goes over a certain limit. I'm still trying to figure out how to expand that formula to the entire column of totals, not only one cell. So anytime a balance total in the entire column is over that limit, another cell will change color. Any way to do that?
Thanks!
OK, let's assume your "limit" is $1,000, and your totals are in column A, then you can select the cell you want to use CF on and enter this formula as the 'CF Rule':
=COUNTIF(A:A,">"&1000)
- Moo
Moo the Dog,
Thank you so much. You're the best! That formula did it. Keep it up!
Glad to help
- Moo
- - - - - - - - - -
A good way to say thanks to those who help is to click on the little star at the lower-left corner of one of the helper's posts. It is always appreciated.
If I may bother again... Just wondering if I can take it one step further. Can I set a conditional format on a cell to gradually change colors as it nears a certain limit? So let's say the maximum limit is set to $1,000, for example up to 40% it will be green, and as it increases it slowly changes to yellow and then red. Is that possible?
If you want to highlight the cells in column A based on their values, select the range of cells containing values, click Conditional Formatting, and select 'Color Scales' : 'Red-Yellow-Green Color Scale'
That is a fast way of doing it, otherwise, you could set up a 3-color scale based on values in the list. To do that,
1) Select the cells you want formatted
2) Click 'Conditional Formatting'
3) Select 'Color Scales' : 'More rules...' : 3-Color Scale (style)
4) Set 'Type' to 'Number' for all three sets (Minimum, Midpoint, & Maximum)
5) Set 'Value' to 400, 700 and 1000 (Min, Mid, Max)
6) Pick colors Green (Min), Yellow (Mid) and Red (Max)
7) Click OK
Should be good to go!
- Moo
Thanks again Moo for your kindness. It seems to be working, although I'd have to test it a little better to see if everything's OK. Now, let's say in another column I want to calculate how much percentage is. For example: Maximum limit is 1000. So next to each cell of column M I want another column N to tell me how much percentage the value is in percentage of 1000.
If I am understanding you correctly, just divide the value in column M by 10 and that will give you a percentage relative to 1000...
For instance if in M2 you have 708, in N2 enter =M2/10. That will result in 70.8, which is the percentage relative to 1000.
- Moo
I believe this one is not giving the needed results. Let me clarify more. I'm using it for credit card expenses. Let's say my total credit limit is $3000. I don't want to use it more than 30% of my maximum $3000, in this example $900. Column M contains the total based on expenses and payments in columns K and L. In column N I'd like to see how much percent the value of column M is against the total 3000. Got it?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks