Solved

Combining daily and monthly data

Posted on 2011-03-14
5
317 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:teylyn
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:
teylyn 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now