Addition/subtraction possible with occasional empty cells?

[StargateFanFromWork]

Is it possible to have formulas that work even if some cells in between are
empty of dollar values? Not every row will necessarily have a dollar
amount. The sheet in question has a section that involves subtraction but
it only works if previous rows are not empty and they are filled one after
the other. In other words, if any rows contain cells that are empty of
value, subsequent calculations underneath don't work.

Was hoping someone could tell me if a) possible to re-write formulas to do
calculations correctly adjusting for any blank cells, and b) what term I can
search for in the archives that will do this?

Thanks.

 

[Tom Ogilvy]

=if(A2<>"",A1-A2,if(A3<>"",A1-A2,if(A4<>"",A1-A4,if(A5<>"',A1-A5,""))))

entered in A6.

 

[StargateFanFromWork]

Tom, Good Afternoon! <g>

I get an error with this code. I've typed in number values into A1 to A5 of
a blank sheet and plugged in your formula into A6, just to see what is
happening so that I'll know how to proceed with my own sheet. I have A6
formatted as general. What am I doing wrong?

Thanks. D

 

[Tom Ogilvy]

Looks like a typo - a single quote in one location rather than a double
quote

=IF(A2<>"",A1-A2,IF(A3<>"",A1-A2,IF(A4<>"",A1-A4,IF(A5<>"",A1-A5,""))))


The formula is not suggested as a specific solution to your problem, since
your the description in your question was so vague that it would be
impossible to infer what your problem might be. This formula will subtract
the first value in the range A2:A5 from the value in A1. It represents a
concept of checking for various conditions and taking appropriate actions.
It may or may not be what you need. Hopefully it will inspire a solution or
result in a specific example of your formulas and problem.

 

[StargateFanFromWork]

Phew! I'd have never figured that out! <g>

Well, this is absolutely neat. I should have given an example like I'm
doing below, however. I'm often reminded that I'm like Kahn in Star Trek
II; I often display my two-dimensional thinking <g>. So, the formula now
"works", but it doesn't give target results. I used easy numbers below to
try to illustrate. Subtraction; A1 is the starting dollar amount. A6, of
course, is the result, so A2 to A5, in this example, are to be subtracted
from A1. With the formula as it stands, A6 gives the result of 90 no matter
what is happening in A2 to A5 as long as one value is in any of these cells.
Deleting any of those cell contents didn't affect the results, in other
words.

A1 = 100 (subtract from)
A2 = 10
A3 = 10
A4 = 10
A5 = 10
A6 = 60
This is the type of result I was hoping to achieve. I'm very sorry that it
was so tough to explain. I should have just given an example like this.
Hopefully XL2K can work with an adjusted formula to give the above type of
result, or like below, depending on the cell contents even when some are
empty.

A1 = 100 (subtract from)
A2 =
A3 = 10
A4 =
A5 = 10
A6 = 80

A1 = 100 (subtract from)
A2 = 10
A3 = 10
A4 = 10
A5 =
A6 = 70


A1 = 100 (subtract from)
A2 =
A3 =
A4 =
A5 =
A6 = 100

Can this be done?

Thanks. D

 

[StargateFanFromWork]

I did find a "dirty" solution that I'm not happy with but that will work
until this problem is resolved. After looking a few times over a couple of
days, I finally found pertinent info in the archives on how how to do
conditional formatting not only on a text string but to work with a cell
outside the one we're putting the cond form in. This is probably easy to
most, but I couldn't get anything to work. (I finally figured out it might
have to do with the order of the cond form and bumped up the formula with
the different cell source to the first place. Finally it worked!)

With the cond form in place, I then put some zeroes in some of the cells in
column F. Column G now keeps a proper tally. It would be easier to have
the formula work properly, but at least this cumbersome work-around "erases"
the results in any cell even only if on the screen in G that has a zero
preceding it in F. That way any repeating dollar amounts are wiped out and
I only see the proper tallies. The printout doesn't work because the
colours don't hide in a black and white printout but I'll try changing to
greyscale colours for the affected cells to see if I get the same results in
the printout as on the screen, that is, of hiding certain dollar values.

At any rate, I'm still interested if there is a way to make the formula work
even with cells that have no input. As mentioned originally, any empty
cells disrupt the tallies and I get blanks from the point after the very
first empty cell.

TIA.


[snip]

With the formula as it stands, A6 gives the result of 90 no matter

what is happening in A2 to A5 as long as one value is in any of these cells.
Deleting any of those cell contents didn't affect the results, in other
words.

A1 = 100 (subtract from)
A2 = 10
A3 = 10
A4 = 10
A5 = 10
A6 = 60
This is the type of result I was hoping to achieve. I'm very sorry that it
was so tough to explain. I should have just given an example like this.
Hopefully XL2K can work with an adjusted formula to give the above type of
result, or like below, depending on the cell contents even when some are
empty.

A1 = 100 (subtract from)
A2 =
A3 = 10
A4 =
A5 = 10
A6 = 80

A1 = 100 (subtract from)
A2 = 10
A3 = 10
A4 = 10
A5 =
A6 = 70


A1 = 100 (subtract from)
A2 =
A3 =
A4 =
A5 =
A6 = 100

Can this be done?

Thanks. D

[snip]

 

[Bob Davison]

Put this in cell A6....
=A1-SUMIF(A2:A5,"<>0")

I did find a "dirty" solution that I'm not happy with but that will work
until this problem is resolved. After looking a few times over a couple
of
days, I finally found pertinent info in the archives on how how to do
conditional formatting not only on a text string but to work with a cell
outside the one we're putting the cond form in. This is probably easy to
most, but I couldn't get anything to work. (I finally figured out it
might
have to do with the order of the cond form and bumped up the formula with
the different cell source to the first place. Finally it worked!)

With the cond form in place, I then put some zeroes in some of the cells
in
column F. Column G now keeps a proper tally. It would be easier to have
the formula work properly, but at least this cumbersome work-around
"erases"
the results in any cell even only if on the screen in G that has a zero
preceding it in F. That way any repeating dollar amounts are wiped out
and
I only see the proper tallies. The printout doesn't work because the
colours don't hide in a black and white printout but I'll try changing to
greyscale colours for the affected cells to see if I get the same results
in
the printout as on the screen, that is, of hiding certain dollar values.

At any rate, I'm still interested if there is a way to make the formula
work
even with cells that have no input. As mentioned originally, any empty
cells disrupt the tallies and I get blanks from the point after the very
first empty cell.

TIA.


[snip]

With the formula as it stands, A6 gives the result of 90 no matter

what is happening in A2 to A5 as long as one value is in any of these cells.
Deleting any of those cell contents didn't affect the results, in other
words.

A1 = 100 (subtract from)
A2 = 10
A3 = 10
A4 = 10
A5 = 10
A6 = 60
This is the type of result I was hoping to achieve. I'm very sorry that it
was so tough to explain. I should have just given an example like this.
Hopefully XL2K can work with an adjusted formula to give the above type
of
result, or like below, depending on the cell contents even when some are
empty.

A1 = 100 (subtract from)
A2 =
A3 = 10
A4 =
A5 = 10
A6 = 80

A1 = 100 (subtract from)
A2 = 10
A3 = 10
A4 = 10
A5 =
A6 = 70


A1 = 100 (subtract from)
A2 =
A3 =
A4 =
A5 =
A6 = 100

Can this be done?

Thanks. D

[snip]

 

[StargateFanFromWork]

Hi, I completely missed your message. Sorry 'bout that.

I'll give this a try when I get home. Thanks! D

Put this in cell A6....
=A1-SUMIF(A2:A5,"<>0")

I did find a "dirty" solution that I'm not happy with but that will work
until this problem is resolved. After looking a few times over a couple
of
days, I finally found pertinent info in the archives on how how to do
conditional formatting not only on a text string but to work with a cell
outside the one we're putting the cond form in. This is probably easy to
most, but I couldn't get anything to work. (I finally figured out it
might
have to do with the order of the cond form and bumped up the formula with
the different cell source to the first place. Finally it worked!)

With the cond form in place, I then put some zeroes in some of the cells
in
column F. Column G now keeps a proper tally. It would be easier to have
the formula work properly, but at least this cumbersome work-around
"erases"
the results in any cell even only if on the screen in G that has a zero
preceding it in F. That way any repeating dollar amounts are wiped out
and
I only see the proper tallies. The printout doesn't work because the
colours don't hide in a black and white printout but I'll try changing to
greyscale colours for the affected cells to see if I get the same results
in
the printout as on the screen, that is, of hiding certain dollar values.

At any rate, I'm still interested if there is a way to make the formula
work
even with cells that have no input. As mentioned originally, any empty
cells disrupt the tallies and I get blanks from the point after the very
first empty cell.

TIA.


[snip]

With the formula as it stands, A6 gives the result of 90 no matter

what is happening in A2 to A5 as long as one value is in any of these cells.
Deleting any of those cell contents didn't affect the results, in other
words.

A1 = 100 (subtract from)
A2 = 10
A3 = 10
A4 = 10
A5 = 10
A6 = 60
This is the type of result I was hoping to achieve. I'm very sorry

that
it

was so tough to explain. I should have just given an example like this.
Hopefully XL2K can work with an adjusted formula to give the above type
of
result, or like below, depending on the cell contents even when some are
empty.

A1 = 100 (subtract from)
A2 =
A3 = 10
A4 =
A5 = 10
A6 = 80

A1 = 100 (subtract from)
A2 = 10
A3 = 10
A4 = 10
A5 =
A6 = 70


A1 = 100 (subtract from)
A2 =
A3 =
A4 =
A5 =
A6 = 100

Can this be done?

Thanks. D

[snip]