Solved

Combining daily and monthly data

Posted on 2011-03-14
5
339 Views
Last Modified: 2012-06-27
In column A I have dates in the format yyyymmdd.  In column B is daily data (just weekdays).  In column C are monthly dates(in format Jan-90), and in column D is monthly data covering the same overall time period as the daily data (ie, January 1990 to December 2010).  I want each cell in column E to be the product of the daily value in column B times the corresponding monthly value in column D.  As an example:

Column A       Column B      Column C       Column D     Column E
19900130        100              Jan-90              5                 500
19900131          90              Feb-90             3                 450
19900201          80              Mar-90             4                 240
       ...
19900228          90              June-92            7                 270
19900301        100              July-92             6                  400

Please keep in mind that the daily data excludes weekend dates.

Thanks!!
0
Comment
Question by:pwflexner
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35132391
Hello,

can you run the explanation by me once again? How come the second row of data results in 450? What is the logic in plain words? The first row is easy, B * D, but I don't quite see how you want to arrive at the results in column E in the other rows.

Could you please upload a spreadsheet that also shows the data types of your columns? I.e. is column C a real date or is it text?

I'm not quite sure I understand this: "in column D is monthly data covering the same overall time period as the daily data (ie, January 1990 to December 2010)"

Please explain with a bit more background and detail.

cheers, teylyn
0
 
LVL 3

Expert Comment

by:Chadinzski
ID: 35132398
So column B is the weekdays that elapsed since ColumnA?  IE if a Saturday and Sunday roll by, the value would be 2?

I'm having trouble with ColumnD. In your example:
19900201          80              Mar-90             4                 240
02= Feb, so Feb+Mar = 2, not 4.

Perhaps renaming Column1, 2 3 etc with names would be easier too.  IE, call ColumnD "ElapsedMonths" if that's what it holds.
0
 

Author Comment

by:pwflexner
ID: 35132804
Teylyn and Chadzinski,

Sorry for the confusion.  The second row of Column E is 450 because the daily value is 90 but the applicable month is still January, which has a monthly value of 5.. So, 90*5=450.  The next row has a daily value of 80 and the applicable month is now February, which has a value of 3, so 80*3=240.

I'm attaching a spreadsheet with the data and will label the columns with a bit more specificity....
Sample.xlsx
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 125 total points
ID: 35132981
Hello,

so your data in columns C and D is really a lookup table. I thought it was related to the row.

Try this in F8 and copy down

=VLOOKUP(DATE(LEFT(A8,4),MID(A8,5,2),1),$C$8:$D$100,2,FALSE)*B8

Adjust the bold part in the formula to cover the complete range of your month value list.

cheers, teylyn

0
 

Author Comment

by:pwflexner
ID: 35133046
Perfect!! Thanks.
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Multiple Open Excel Spreadsheets 12 41
Left trim cells in column A Excel vba 2 32
Request to review costing formula 3 36
Excel filter on tab not showing any entries? 5 22
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question