Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Combining daily and monthly data

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
pwflexner
Asked:
pwflexner
  • 2
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
ChadinzskiCommented:
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
 
pwflexnerAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
pwflexnerAuthor Commented:
Perfect!! Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now