is it possible to have excel insert zeros into data??

[thuyhong]

Hello -

Not sure if this is where I'm suppose to post this - I am in
desperatly need of help

I have a workbook that have about 10companys names and 7 thousand data
- going back to 1999 - i need to know how i can insert 0 (zeros) into
missing data... the workbook is outline like this --->

A B C
D E F
G
Dates Products Company Amount Daily
Total Weekly Total Monthly total
1/6/2006 Cream A 25
1/6/2006 Red B 50
1/7/2006 Cream A 125
1/8/2006 Red B 152
1/8/2006 Red B 30
1/10/2006 Cream A 29
1/11/2006 Red B 25
1/11/2006 Red B 37
1/11/2006 Cream A 45



In column E-G I already have the total set up to give me the average -
I realize that some of the day's the company does not have data in and
i would like excel to insert those empty days with 0 (zeros) into the
total

with the above example I would like excel to see that on 1/7/2006 only
product Cream and company A have data --- so it will insert company B
into the spreadsheet and in the total put 0 (zero)

is this possible??? please help...

 

[aidan.heritage]

Hello -

Not sure if this is where I'm suppose to post this - I am in
desperatly need of help

I have a workbook that have about 10companys names and 7 thousand data
- going back to 1999 - i need to know how i can insert 0 (zeros) into
missing data... the workbook is outline like this --->

A B C
D E F
G
Dates Products Company Amount Daily
Total Weekly Total Monthly total
1/6/2006 Cream A 25
1/6/2006 Red B 50
1/7/2006 Cream A 125
1/8/2006 Red B 152
1/8/2006 Red B 30
1/10/2006 Cream A 29
1/11/2006 Red B 25
1/11/2006 Red B 37
1/11/2006 Cream A 45

In column E-G I already have the total set up to give me the average -
I realize that some of the day's the company does not have data in and
i would like excel to insert those empty days with 0 (zeros) into the
total

with the above example I would like excel to see that on 1/7/2006 only
product Cream and company A have data --- so it will insert company B
into the spreadsheet and in the total put 0 (zero)

is this possible??? please help...

It is possible, but why do you want to - why not simply use COUNTA to
get the total number of lines, and SUM to get the value and divide one
into the other - granted, a slightly longer formula than AVERAGE, but
it will produce the result you want

=sum(range)/counta(RangeTwo)

 

[Peter T]

Manually, select your data cells including currently blank cells you will
want filled with zeros.

F5 > Special > Blanks

If your mixed data & blank cells is as I imagine you've probably got a lot
of non-contiguous areas of blank cells selected.

Put the cursor in the input-bar, type a 0, Ctrl-Enter to fill with zeros.

I don't understand your second question.

Regards,
Peter T