Prev: blocked file format
Next: vlookup and if, I think
From: George on 25 Apr 2010 20:08
I have two columns of dollar amounts I want to add, E & F,and subtract the
third, G, and the results in column H.

So I entered the formula =Sum(E2+F2-G2) down column H and that works but it
automatically enters $0.00 down the column.

So I entered the following formula down column H and that got rid of the
$0.00 unless there is a value in E, F or G but if the results just happens to
total $0.00 then column H remains blank, I don't want that.

=IF(E2+F2-G2<>0,(E2+F2-G2),"")

How can I tweak this formula to leave column H only blank when there isn't
anything entered.

Thanks
From: Emece on 25 Apr 2010 20:29
George, assuming the case is that sometimes you have E,F, and G with no data,
this formula should help:

=IF(COUNTA(E2:G2)>0;(E2+F2-G2);" ")

Regards,
Emece.-

"George" wrote:

> I have two columns of dollar amounts I want to add, E & F,and subtract the
> third, G, and the results in column H.
>
> So I entered the formula =Sum(E2+F2-G2) down column H and that works but it
> automatically enters $0.00 down the column.
>
> So I entered the following formula down column H and that got rid of the
> $0.00 unless there is a value in E, F or G but if the results just happens to
> total $0.00 then column H remains blank, I don't want that.
>
> =IF(E2+F2-G2<>0,(E2+F2-G2),"")
>
> How can I tweak this formula to leave column H only blank when there isn't
> anything entered.
>
> Thanks
From: George on 25 Apr 2010 20:55
Emece - If tried to enter the formula in column H and I get an error message
that it contains a typo, won't except it.

George


"Emece" wrote:

> George, assuming the case is that sometimes you have E,F, and G with no data,
> this formula should help:
>
> =IF(COUNTA(E2:G2)>0;(E2+F2-G2);" ")
>
> Regards,
> Emece.-
>
> "George" wrote:
>
> > I have two columns of dollar amounts I want to add, E & F,and subtract the
> > third, G, and the results in column H.
> >
> > So I entered the formula =Sum(E2+F2-G2) down column H and that works but it
> > automatically enters $0.00 down the column.
> >
> > So I entered the following formula down column H and that got rid of the
> > $0.00 unless there is a value in E, F or G but if the results just happens to
> > total $0.00 then column H remains blank, I don't want that.
> >
> > =IF(E2+F2-G2<>0,(E2+F2-G2),"")
> >
> > How can I tweak this formula to leave column H only blank when there isn't
> > anything entered.
> >
> > Thanks
From: Teethless mama on 25 Apr 2010 21:06
=IF(COUNT(E2:G2)>0,E2+F2-G2,"")


"George" wrote:

> Emece - If tried to enter the formula in column H and I get an error message
> that it contains a typo, won't except it.
>
> George
>
>
> "Emece" wrote:
>
> > George, assuming the case is that sometimes you have E,F, and G with no data,
> > this formula should help:
> >
> > =IF(COUNTA(E2:G2)>0;(E2+F2-G2);" ")
> >
> > Regards,
> > Emece.-
> >
> > "George" wrote:
> >
> > > I have two columns of dollar amounts I want to add, E & F,and subtract the
> > > third, G, and the results in column H.
> > >
> > > So I entered the formula =Sum(E2+F2-G2) down column H and that works but it
> > > automatically enters $0.00 down the column.
> > >
> > > So I entered the following formula down column H and that got rid of the
> > > $0.00 unless there is a value in E, F or G but if the results just happens to
> > > total $0.00 then column H remains blank, I don't want that.
> > >
> > > =IF(E2+F2-G2<>0,(E2+F2-G2),"")
> > >
> > > How can I tweak this formula to leave column H only blank when there isn't
> > > anything entered.
> > >
> > > Thanks
From: Dave Peterson on 25 Apr 2010 21:07
Try using commas instead of the semicolons:

=IF(COUNTA(E2:G2)>0,E2+F2-G2,"")

I removed the ()'s from the E2+F2-G2 portion and I removed the space from the
final string.



George wrote:
>
> Emece - If tried to enter the formula in column H and I get an error message
> that it contains a typo, won't except it.
>
> George
>
> "Emece" wrote:
>
> > George, assuming the case is that sometimes you have E,F, and G with no data,
> > this formula should help:
> >
> > =IF(COUNTA(E2:G2)>0;(E2+F2-G2);" ")
> >
> > Regards,
> > Emece.-
> >
> > "George" wrote:
> >
> > > I have two columns of dollar amounts I want to add, E & F,and subtract the
> > > third, G, and the results in column H.
> > >
> > > So I entered the formula =Sum(E2+F2-G2) down column H and that works but it
> > > automatically enters $0.00 down the column.
> > >
> > > So I entered the following formula down column H and that got rid of the
> > > $0.00 unless there is a value in E, F or G but if the results just happens to
> > > total $0.00 then column H remains blank, I don't want that.
> > >
> > > =IF(E2+F2-G2<>0,(E2+F2-G2),"")
> > >
> > > How can I tweak this formula to leave column H only blank when there isn't
> > > anything entered.
> > >
> > > Thanks

--

Dave Peterson
 |  Next  |  Last
Pages: 1 2
Prev: blocked file format
Next: vlookup and if, I think