need help with a formula or conditional formatting

[megbowlstrike]

I have a cell (A35) that displays the sum of the cells in range
A9:A33. The cells in this range are pre-populated based off another
cell's data divided by 24. Cell A35 is set to show a max total of
$230,000 even if the cells add up to more than that. However, the
cells in column A may add up to $230,000 before reaching A33. If that
happens, I'd like the cells that are left (haven't been added yet) to
display a 0 or dash. I am thinking that there needs to be some sort
of formula running in the background that keeps a running total of
column A. Then once the total of column A has exceeded $230,000, the
rest of the cells in the range in column A need to display a 0 or
dash. I hope that makes sense--it's difficult to put it into words.
Thanks!

 

[Per Erik Midtrød]

I have a cell (A35) that displays the sum of the cells in range
A9:A33. The cells in this range are pre-populated based off another
cell's data divided by 24. Cell A35 is set to show a max total of
$230,000 even if the cells add up to more than that. However, the
cells in column A may add up to $230,000 before reaching A33. If that
happens, I'd like the cells that are left (haven't been added yet) to
display a 0 or dash. I am thinking that there needs to be some sort
of formula running in the background that keeps a running total of
column A. Then once the total of column A has exceeded $230,000, the
rest of the cells in the range in column A need to display a 0 or
dash. I hope that makes sense--it's difficult to put it into words.
Thanks!

If I have understood correctly this is fairly simple.
Leave the formula in A9 as it is, in a10 replace your formula with
something like:
=IF(SUM(A$9:A9)>230000;0;"Your original formula goes here")

Per Erik

 

[megbowlstrike]

If I have understood correctly this is fairly simple.
Leave the formula in A9 as it is, in a10 replace your formula with
something like:
=IF(SUM(A$9:A9)>230000;0;"Your original formula goes here")

Per Erik

Thanks! I actually just came up with that as well! Now is there a
way to do this: Let's use A17 as an example. The formula I have in
cell A17 is "=IF((SUM(A9:A16))>230000,0,(B6/24))". Cells A9:A17
actually add up to $233,000 but since the formula stops at A16, cell
A17 displays B6/24. How would I go about displaying the difference
between the $233,000 and $230,000 in cell A17? In other words, I
don't want to display the entire total of B6/24 when only part of this
amt makes it go over the $230,000. Does that make any sense?

 

[megbowlstrike]

Thanks! I actually just came up with that as well! Now is there a
way to do this: Let's use A17 as an example. The formula I have in
cell A17 is "=IF((SUM(A9:A16))>230000,0,(B6/24))". Cells A9:A17
actually add up to $233,000 but since the formula stops at A16, cell
A17 displays B6/24. How would I go about displaying the difference
between the $233,000 and $230,000 in cell A17? In other words, I
don't want to display the entire total of B6/24 when only part of this
amt makes it go over the $230,000. Does that make any sense?- Hide quotedtext -

- Show quoted text -

I came up with this and it appears to be working. Do you see any
problems?

=IF((SUM(A9:A16))>=230000,0,(230000-(SUM(A9:A16))))

 

[megbowlstrike]

I came up with this and it appears to be working. Do you see any
problems?

=IF((SUM(A9:A16))>=230000,0,(230000-(SUM(A9:A16))))- Hide quoted text -

- Show quoted text -

Okay I discovered a problem with the formula. It works on the cells
after the total has exceeded $230,000 but it doesn't work on the cells
prior to that. In those cells it displays (SUM(A9:A16)) but I want it
to display (B6/24). How do I do that? I'm thinking of an if/then or
an &/if statement.

 

[Per Erik Midtrød]

I came up with this and it appears to be working. Do you see any
problems?

=IF((SUM(A9:A16))>=230000,0,(230000-(SUM(A9:A16))))

I don't understand how that formula could work for the cells above
A17.
Maybe I'm missing something.

Per Erik

 

[megbowlstrike]

I don't understand how that formula could work for the cells above
A17.
Maybe I'm missing something.

Per Erik- Hide quoted text -

- Show quoted text -

I'm having that problem too. Do you know how I could alter the
formula so that it would work for the cells above A17 (in this case)?

 

[Per Erik Midtrød]

I'm having that problem too. Do you know how I could alter the
formula so that it would work for the cells above A17 (in this case)?

As far as I can tell this one is working:
=IF(SUM(A$9:A14)+B6/24>230000;230000-SUM(A$9:A14);B6/24)

Per Erik

 

[megbowlstrike]

As far as I can tell this one is working:
=IF(SUM(A$9:A14)+B6/24>230000;230000-SUM(A$9:A14);B6/24)

Per Erik- Hide quoted text -

- Show quoted text -

Erik thanks so much for your help! Everything appears to be
working.