I have data from rows 5-3158 (part #'s) and columns B thru M (represents
months), that was populated using a pivot table. Some part numbers were not
puchased every month so the pivot table didn't populate anything, so when I
try to take the average of the row it doesn't work correctly because if it
only populates two columns it takes the average of two instead of 12. I need
to have zero's in the empty cells to get an average over 12 months.

Is there a function to populate these empty cells with "0" so I can get my
correct averages?

Thanks,
BAS

very easy:

1. Select the area of cells
2. Edit Goto... Special Blanks
3. 0 CNTRL-ENTER

inserting the zero with a CNTRL-ENTER rather then the usual ENTER key will
put the zero into all the blank cells in the area.
--
Gary''s Student - gsnu200766


"BAS" wrote:

I have data from rows 5-3158 (part #'s) and columns B thru M (represents
months), that was populated using a pivot table. Some part numbers were not
puchased every month so the pivot table didn't populate anything, so when I
try to take the average of the row it doesn't work correctly because if it
only populates two columns it takes the average of two instead of 12. I need
to have zero's in the empty cells to get an average over 12 months.

Is there a function to populate these empty cells with "0" so I can get my
correct averages?

Thanks,
BAS

If the data you mention is actually a Pivot Table... then all you need to do
is launch the Pivot Table Wizard and Click on "Options", there will be a
field there that will read
For empty cells, show:

Make sure that the box to its left is ticked and enter "0" on the field to
its right.

Click "OK" and then "Finish"

That will do the trick.

If the table you mention is not an actual Pivot Table, but rather a copy of
a pivot table (not linked to the original data).
Do this:
I assume that actual data starts on row 5 and that labels are on row 4.
On cell N5 type: =IF(ISBLANK(B5),0,B5)
Then copy that formula horizontaly all the way out to cell Y5.
Then select the range n5:y5 (all the formulas you just copied/pasted)...
copy that range and paste it vertically all the way down to row 3158.

That will duplicate your data on range b5:m3158 but will replace all blank
cells with zeroes.

Hope this helps.
Sebastian

"BAS" wrote:

I have data from rows 5-3158 (part #'s) and columns B thru M (represents
months), that was populated using a pivot table. Some part numbers were not
puchased every month so the pivot table didn't populate anything, so when I
try to take the average of the row it doesn't work correctly because if it
only populates two columns it takes the average of two instead of 12. I need
to have zero's in the empty cells to get an average over 12 months.

Is there a function to populate these empty cells with "0" so I can get my
correct averages?

Thanks,
BAS

It worked..... THanks!

"Gary''s Student" wrote:

very easy:

1. Select the area of cells
2. Edit Goto... Special Blanks
3. 0 CNTRL-ENTER

inserting the zero with a CNTRL-ENTER rather then the usual ENTER key will
put the zero into all the blank cells in the area.
--
Gary''s Student - gsnu200766


"BAS" wrote:

I have data from rows 5-3158 (part #'s) and columns B thru M (represents
months), that was populated using a pivot table. Some part numbers were not
puchased every month so the pivot table didn't populate anything, so when I
try to take the average of the row it doesn't work correctly because if it
only populates two columns it takes the average of two instead of 12. I need
to have zero's in the empty cells to get an average over 12 months.

Is there a function to populate these empty cells with "0" so I can get my
correct averages?

Thanks,
BAS