0

I have 6 columns and 3 equations : E*F=G, H*G=I & G+I=J

I don't want zeros to print if a cell in F or H is empty.

Currently I have these equations and everything is dependent on the F & H Cell:

column G: IF(ISBLANK(F22),"",E22*F22)

Column I: IF(ISBLANK(H22),"",H22*G22)

Column J: IF(ISBLANK(F22),K22,G22)+IF(H22>0,+I22) Where K22 is an empty cell.

without using K, the result was #Value! if F is blank.

How do I get nothing to print in Column J if cells in Column F are empty.
Presently "zeros" print in Column J.

DavidPostill
  • 153,128
  • 77
  • 353
  • 394
Betty
  • 1

2 Answers2

2

Your problem is that the cells aren't blank. The cells are "" which we see as blank because it is an empty text string but is not actually blank. That leads to silliness like 0 + "" in your formula for J.

To fix the I formula, try this: IF(ISBLANK(H22),"",IF(ISBLANK(F22),"",H22*G22))

I'm not sure what you're trying to accomplish in J though. It looks like you forgot part of the formula (see that last bit IF(H22>0,+I22)?)

Tim
  • 204
  • 3
  • 13
  • Thx Tim. If F is blank, then J is blank. J = G + I, but when H is blank, then I is blank, & I get #Value!. – Betty Sep 04 '15 at 02:22
  • Thx Tim. If F is blank, then G, H, I and J is blank, not zeros. If F>0, then EF=G where G is $ rate. And if H is blank, then I is blank where H is a percentage, and I is the percentage in $. when H is blank, then J=G. When H>0, then J=G+I -- I'm adding the percentage to the rate. If H is blank, then I am only showing the rate (G). Thank you for your help. – Betty Sep 04 '15 at 02:45
1

I'm not clear what you want.  You say,

I don't want zeros to print if a cell in F or H is empty.

Based on that, it seems to me that you want to have

=IF(OR(ISBLANK(F22),ISBLANK(H22)), "", G22+I22)

in J22.  See also Display Blank when Referencing Blank Cell in Excel 2010 for other approaches.

Community
  • 1
Scott - Слава Україні
  • 21,399
  • 46
  • 64
  • 121
  • Thx Scott: as you can tell logic is not my strong point. If F is blank, then H&J is blank. If F>0, then EF=G. If H>0, then GH=I & G+I=J. If H is blank, then J=G I keep getting #Value!. Thank you in advance for your help. – Betty Sep 04 '15 at 02:12