need help with a formula or conditional formatting
 

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!

need help with a formula or conditional formatting
 

On Dec 4, 8:56 pm, wrote:
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

need help with a formula or conditional formatting
 

On Dec 4, 2:22 pm, "Per Erik Midtrød" wrote:
On Dec 4, 8:56 pm, wrote:

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

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?

need help with a formula or conditional formatting
 

On Dec 4, 2:42 pm, wrote:
On Dec 4, 2:22 pm, "Per Erik Midtrød" wrote:





On Dec 4, 8:56 pm, wrote:

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

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 quoted text -

- 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))))

need help with a formula or conditional formatting
 

On Dec 4, 2:59 pm, wrote:
On Dec 4, 2:42 pm, wrote:





On Dec 4, 2:22 pm, "Per Erik Midtrød" wrote:

On Dec 4, 8:56 pm, wrote:

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

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 quoted text -

- 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))))- 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.

need help with a formula or conditional formatting
 

On Dec 4, 9:59 pm, wrote:
On Dec 4, 2:42 pm, wrote:



On Dec 4, 2:22 pm, "Per Erik Midtrød" wrote:

On Dec 4, 8:56 pm, wrote:

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

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 quoted text -

- 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))))

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

Per Erik

need help with a formula or conditional formatting
 

On Dec 4, 3:23 pm, "Per Erik Midtrød" wrote:
On Dec 4, 9:59 pm, wrote:





On Dec 4, 2:42 pm, wrote:

On Dec 4, 2:22 pm, "Per Erik Midtrød" wrote:

On Dec 4, 8:56 pm, wrote:

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

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 quoted text -

- 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))))

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)?

need help with a formula or conditional formatting
 

On Dec 4, 10:45 pm, wrote:
On Dec 4, 3:23 pm, "Per Erik Midtrød" wrote:



On Dec 4, 9:59 pm, wrote:

On Dec 4, 2:42 pm, wrote:

On Dec 4, 2:22 pm, "Per Erik Midtrød" wrote:

On Dec 4, 8:56 pm, wrote:

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

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 quoted text -

- 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))))

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)?

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

Per Erik

need help with a formula or conditional formatting
 

On Dec 4, 4:05 pm, "Per Erik Midtrød" wrote:
On Dec 4, 10:45 pm, wrote:





On Dec 4, 3:23 pm, "Per Erik Midtrød" wrote:

On Dec 4, 9:59 pm, wrote:

On Dec 4, 2:42 pm, wrote:

On Dec 4, 2:22 pm, "Per Erik Midtrød" wrote:

On Dec 4, 8:56 pm, wrote:

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

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 quoted text -

- 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))))

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)?

As far as I can tell this one is working:
=IF(SUM(A$9:A14)+B6/24230000;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. :)