in [General]

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 |