how do i get excel to add 0s???

CThai

Active Member
Joined
Mar 18, 2007
Messages
295
i have a workbook with missing dates and would like excel to insert any missing dates and add 0's into that missing dates if there is no data...

for example
Book1
ABCD
1DateCompany NameTotal
21/2/2006A25
31/2/2006A35
41/3/2006B125
51/5/2006B20
61/5/2006B185
71/7/2006A200
81/8/2006B250
91/10/2006B250
101/10/2006A350
Sheet1
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

CThai

Active Member
Joined
Mar 18, 2007
Messages
295
with the above image - i would like to insert missing dates with 0 in total and also put in company's name --... so on date 1/2/2006 only company A name have total I would like it to recognize that company B didn't have total and insert Company B into 1/2/2006 and put 0's in there so that i can average it out and graph it accordingly...


am i confusing you??? please help.. please
 
Upvote 0

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
so for 1/3/2006.. there is no company A

would it stick that in there and add zeros?
 
Upvote 0

CThai

Active Member
Joined
Mar 18, 2007
Messages
295
Yes - if the company A or B have no total it would stick
1) the company's name
2) 0's data and
3) dates...
 
Upvote 0

CThai

Active Member
Joined
Mar 18, 2007
Messages
295
So if company A have a total on 1/2/2006 and company B dont - then insert 1/2/2006 Company B with 0's in the total...

is this even possible?? i'm asking excel to insert not only the dates but the company names and the total - into anything that missing data going back since 1999...

is there light at the end of the tunnel??
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
So if company A have a total on 1/2/2006 and company B dont - then insert 1/2/2006 Company B with 0's in the total...

is this even possible?? i'm asking excel to insert not only the dates but the company names and the total - into anything that missing data going back since 1999...

is there light at the end of the tunnel??

The light is to have formulas to treat missing data as missing, that is, as zeroes. They most often do so automatically...
 
Upvote 0

CThai

Active Member
Joined
Mar 18, 2007
Messages
295
Hi Aladin -

The light is to have formulas to treat missing data as missing, that is, as zeroes. They most often do so automatically...

not sure what you mean? would it show up as 0 (zeor) since i will be using this to graph...
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Hi Aladin -

The light is to have formulas to treat missing data as missing, that is, as zeroes. They most often do so automatically...

not sure what you mean? would it show up as 0 (zeor) since i will be using this to graph...

You could AutoFilter the data area with custom: >0 OR <0, then build the graph.
 
Upvote 0

CThai

Active Member
Joined
Mar 18, 2007
Messages
295
You could AutoFilter the data area with custom: >0 OR <0, then build the graph.

I autofilter the data but still not getting 0 (zeros) to show up on the missing data... is there something else i have to do?
 
Upvote 0

Forum statistics

Threads
1,191,125
Messages
5,984,786
Members
439,911
Latest member
dk73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top