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.

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

entered in A6.

--
Regards,
Tom Ogilvy


"StargateFanFromWork" wrote:

> 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, 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" <(E-Mail Removed)> wrote in message
news:71EE388E-17D2-45F9-BB6A-(E-Mail Removed)...
> =if(A2<>"",A1-A2,if(A3<>"",A1-A2,if(A4<>"",A1-A4,if(A5<>"',A1-A5,""))))
>
> entered in A6.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "StargateFanFromWork" wrote:
>
> > 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.

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.

--
Regards,
Tom Ogilvy



"StargateFanFromWork" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:71EE388E-17D2-45F9-BB6A-(E-Mail Removed)...
>> =if(A2<>"",A1-A2,if(A3<>"",A1-A2,if(A4<>"",A1-A4,if(A5<>"',A1-A5,""))))
>>
>> entered in A6.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "StargateFanFromWork" wrote:
>>
>> > 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.
>
>

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


"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:71EE388E-17D2-45F9-BB6A-(E-Mail Removed)...
> >> =if(A2<>"",A1-A2,if(A3<>"",A1-A2,if(A4<>"",A1-A4,if(A5<>"',A1-A5,""))))
> >>
> >> entered in A6.
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "StargateFanFromWork" wrote:
> >>
> >> > 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.
> >
> >
>
>

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.

"StargateFanFromWork" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...

[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]

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

"StargateFanFromWork" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>
> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>
> [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]
>
>

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

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

"Bob Davison" <(E-Mail Removed)> wrote in message
news:%23n1%(E-Mail Removed)...
> Put this in cell A6....
> =A1-SUMIF(A2:A5,"<>0")
>
> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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.
> >
> > "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> >
> > [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]
> >
> >
>
>