Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combining daily and monthly data

Posted on 2011-03-14
5
Medium Priority
?
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 50
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 (Microsoft MVP / EE MVE) earned 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

721 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