Excel.how to avoid turning blanks to zero after manipulation?

Question

example: I have a table of numbers and blanks. I want to multiply all the mumbers by a certain number. so I make a copy of the table, make a formula that multiply a number from the first table and give me the answer in the second table and then I apply it on all the table by dragging it. the problem is that this action also includes all the blanks which excel refers to as zeros, hecnce giving me the result zero in all the cells in the second table that corresponds to the blank cells in the first table. I don't want to see a lot of zeros in my table. it makes it more difficult to see where there are values and where there are none. I hope this explanation was clear. thank u

Answer 1

in place of =B1 in your formula
use:
=IF(B1="","",B1)

Answer 2

If I understand your question correctly, you want to be able to use numbers in one table (with blank rows in between) to calculate another set of numbers in a second table without capturing the blank fields. I suggest writing an "IF" statement formula rather then just copy over and write a multiplication formula.

For example, in the first cell of the second table you are creating use this formula: =IF(B1>0, B1*number,""). Then just drag formula down the rows to finish the table. This formula basically states to take the number in the table if it's greater then zero, and multiply that by my other number; if less then zero then the field should stay blank.
Here's the details of the formula:

B1 = the cell where the first number you want from your first table to be multiplied.

number = the number you want the first table values to be multiplied with.

"" = (two quotation marks, no spaces) -this indicates to excel that if the field is blank, the result in the second table should also be blank.


Hope this helps.

Answer 3

Display or hide zero values
By default, zero values are displayed as 0's (zeros). You can set options to display all zero values on a worksheet as blank cells, or you can format selected cells to hide the 0's.
Display or hide zero values on an entire worksheet
On the Tools menu, click Options, and then click the View tab.
Do one of the following:
To display zero (0) values in cells, select the Zero values check box.
To display zero values as blank cells, clear the check box.

Hide zero values in selected cells
Caution This topic applies a format to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the General number format.
1. Select the cells that contain the zeros (0's) you want to hide.
2. On the Format menu, click Cells, and then click the Number tab.
3. In the Category list, click Custom.
4. In the Type box, type 0;-0;;@
Notes
· The hidden values appear only in the formula bar — or in the cell if you edit within the cell — and are not printed.
· To display hidden values again, select the cells, click the Cells command on the Format menu, and then click the Number tab. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.

Answer 4

On the 2nd table, do a search and replace all zeros. Make sure you choose 'whole words'