Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to change the background of a cell as long as the running total in that column from start to that cell is less than a limit specified in a different cell.

Conceptually, the formula should look like this:

=SUM(FIRST_CELL_IN_COLUMN:THIS_CELL)<LIMIT_CELL

or, in more concrete terms:

=SUM($C$6:THIS_CELL)<$F$10

For example, suppose the value in cell F10 is 19, and column C looks like this (starting from C6):

10
5
3
2
1

Now, for illustrative purposes, let's compute the running total for each cell in the column (note that this column does not actually exist in the spreadsheet):

10
15
18
20
21

Remember, I want to change the background if the running total is less than the specified limit. So, for this example, the first 3 cells should have their backgrounds changed.

My problem is that I don't know how to write the reference for THIS_CELL. This is what I've tried so far:

=SUM($C$6:INDIRECT(ADDRESS(ROW(),COLUMN())))<$F$10

...but I get this error:

error dialog

Any suggestions on how I can make this work the way I want, or is that error message indicating that it's impossible? I know I could cheat by adding a "running total" column and configure conditional formatting based on those results, but I want to avoid that if at all possible.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You just need to set your cell references carefully in the formula for formatting the sum column. Assuming that the sum values start in cell A1, highlight the sum column values and set the formula for conditional formatting as:

=SUM($A$1:A1)<$F$10
share|improve this answer
    
You're right; I was making it harder than it needed to be. You got me pointed in the right direction, then I realized it was always off by one cell so I actually needed =SUM($A$1:A1)-A1<$F$10. Thanks! –  rob May 17 '13 at 2:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.