TipH1015


NewDeal Hot Tip 1015

NewCalc

NewCalc, Zero Column Width

I was struggling with a problem in my business invoice template.
I had columns quantity (C), price (H), and amount (I).
I multiplied the quantity by the price and placed the result in the amount. =IF(C22*H22, C22*H22, „“)
This leaves the cell blank if the result is zero.
Then I needed to add up the amount column, =SUM(I22:I41), for the subtotal cell.
Oops, that gives a #TYPE# error.
So I changed the amount column to =IF(C22*H22, C22*H22, 0).
This took care of the #TYPE# error, but put zeros in the cells where I want blanks.
Then I discovered what seems to be the ultimate solution.

  • I changed I22:I41 back to =IF(C22*H22, C22*H22, „“).
    This left I42 with the #TYPE# error.
  • Then I built a new range in column J, J22:J41.
    This range uses =IF(C22*H22, C22*H22, 0).
  • Then I changed the subtotal cell, I42, to =SUM(J22:J41).
  • This removes the #TYPE# error and gives a good result there.

Here is the cool part:

  • I selected the J column,
  • I selected the Cell menu, chose Cell Width, and made the J column zero (0) pixels wide. You’ll never see column J and it won’t print.

Last Modified 28 Feb 1999
©2000 NewDeal Inc. All rights reserved


BlankHomeBlank
Eine Seite zurück

Kommentare sind geschlossen.