Converting Monthly data to Weekly data in Excel

For something that’s not so catchy, this is an awfully innovative solution I found. Sometimes, what’s out there on Excel surprises you to the extent of making you feel like a noob every now and then.

Most usually, data for modeling comes in the form of Monthly data, which needs to be converted to Weekly figures. Where does the problem lie? Take a look at the data and you’ll know right away.

Excel Monthly-Weekly

Here’s a neat trick that solves the problem with one formula – that’s right, just ONE formula!

=IF(C$2<$B3,MAX(0,C$2-EOMONTH($B3,-1)+6),MAX(0,MIN(7,EOMONTH($B3,0)-C$2+1)))/(EOMONTH($B3,0)-$B3+1)

And you get this -

Blog - Excel v1
Blog - Excel

I must give credits to this guy or “AlphaFrog” it is, he was the basis for such a complete one step solution! Take a look at this thread -

http://www.mrexcel.com/forum/excel-questions/627101-converting-data-monthly-weekly.html

I’m attaching a snapshot of the thread just in case. This is good stuff!

Example - Blog

Round “time” in Excel

The “Time” format is one of those least understood formats, at least used to be for me! Until, I had to work on it extensively for one of my projects.. And it’s actually not that complex.. :)

Here’s a quick snapshot on how the Time format works -
- The DATE & TIME is integrated to be represented by one number – a floating point number!
- The decimal part characterizes the time, while the integer part the date.
- [0,1] = [00:00 HRS,00:00 HRS + 1 day] This is the set that determines the time of the day. For eg, 0.5 = 12 NOON.
- Hence, “1/1/2012 12:00:00 PM” is nothing but “40909.5″
- While working with manipulations over time data, understanding the underlying number structure proves critical to work around it. Some eg.s include rounding time to the nearest hour/half hour or quarter hour.. Or finding the difference in time between one day and the next.. etc..

Now that the background’s covered, let’s jump in into the post’s topic – Rounding Time in Excel!

Here’s a list of formulae that do the trick -
= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440
= TIME(HOUR(A2), ROUND((MINUTE(A2)/60)*4, 0) * 15, 0)
= ROUND(A1*(24*60/15),0)/(24*60/15)
= INT(A1*(24*60/15),0)/(24*60/15)
= B2-MOD(B2,15/24/60)
= MROUND(MOD(B2,1)*60,15) (Note: Excel 2007 Addin – Not tested)

All of these work, and they resort to using the decimal numbers to get to the TIME manipulations. Have a go at some of these formula, and you might be able to understand how it works!

Where EXCEL fails?!

All the while it’s been Excel can do this, and Excel can do that. But what are its pitfalls? Where does it fail? What should we beware?
Surprisingly, there’s a whole community that’s at this topic! And I only recently stumbled upon them. That while trying to learn the new software “R”.

Here’s a link to Excel’s shortfalls, something that only authentic Statistical softwares are capable of.

http://biostat.mc.vanderbilt.edu/twiki/bin/view/Main/ExcelProblems

I’d like to take the time to highlight some of these below -

1. Excel’s greatest boon – the spreadsheet page – is it’s greatest bane! Formulae and Values in the same cell, with no line of demarcation between the functions and the end results. Some operations such as sorting result in erroneous output.

2. Data Validation is a big problem

3. Computational complexity and inconsistencies, not to forget the time.

4. Bad graphing processes (I always thought Excel graphs were good!)

5. Excel Statistics is anything but Statistics! The probabilities, accuracy, plots, regression etc.. nothing matches with Statistical Software packages. (RAND function is potentially a great bug)

6. Solver is not something that can be relied upon, convergence issues.

7. No “HISTORY” to track the list of data actions/manipulations for ease of debugging or other reasons.

And the list goes on.. I’ve only scratched the surface I believe, but these are pretty much the high level accusations.

And now, what I’d like to take away from all this -

I know and understand that every software has its set of pluses and limitations, and Excel is no exception. It’s Microsoft, it’s unpredictable, it’s got bugs, it’s not perfect.. you can go on..
But it’s still the most widely used and understood piece of software, easy to use since we already know it so well, and works wonders on quick visual inferences. And Excel is powerful enough usually to do stuff that is beyond the average tasks, and I’ve myself used it for purposes that are beyond what I thought possible.

IF anything, it’s a great software, and currently, the easiest at handling data, charting and computation. And as long as we don’t rely on using hardcore Statistics using Excel’s built-in tools, I don’t see any harm why we can’t boast of Excel’s functionality in other domains! Also, most stuff above about Excel’s limitations on data complexity or computational complexity is something we already know, and here’s the best part – once used to Excel, we end up working around it always. ALWAYS! We just know where it hits the limit. It’s like having this old piece of machine and knowing how to use it. Agreed there are better tools out there, but currently, this one rules the majority of the population and until a time when the others take over, we can still boast of Excel’s capabilities and keep learning newer stuff to boost our skills and make our everyday life easier…

Coloring the scatter plot in Excel in groups

Scatter plots are generally a nice tool to represent data, especially ones where we’d like to show a pattern of some kind. But what happens when we have a sub-pattern of data which we need to plot to understand the pattern in the scatterplot?

Let’s take an example here. This is how the data looks -

Name X – Axis Y – Axis
Region1 3% 17%
Region1 4% 34%
Region1 3% 14%
Region1 3% 37%
Region1 4% 28%
Region2 6% 23%
Region2 5% 30%
Region2 7% 33%
Region2 6% 25%
Region2 6% 30%
Region3 7% 50%
Region3 7% 70%
Region3 6% 59%
Region3 7% 48%
Region3 8% 59%

 

and the scatterplot like this…

Chart1

 

 

 

 

 

 

 

But we’ve got more information that could be plotted, only it’s not directly possible in Excel. The regions are a second level of data that could aid us in plotting and visualizing the data, maybe there some kind of pattern within the grouping that we’re not able to see currently from this chart!

Take a look at how this might look -

Chart2

And what a difference it seems to make on what we can infer from the chart!

Wouldn’t it be great if we could do this instantly? Well, excel doesn’t seem to be supporting such a feature yet directly from the menus, but I guess we’ve always got the folks who just can’t keep it quiet when it comes to tweaking!!

Here’s a nice link that shows how this can be done!

http://www.clear-lines.com/blog/post/Excel-ScatterPlot-with-labels-colors-and-markers.aspx

As for me, I’ve got my own piece of code, not the most robust & efficient one, but definitely works! I’ll probably put that up sometime soon…

Enjoy the excel experience…

THANK YOU to all my READERS! HAVE A GREAT 2013~!

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 7,600 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 13 years to get that many views.

Click here to see the complete report.

Extracting Text from Right of String in Excel

How often have you wanted to badly extract text from the right of a text, just the way the LEFT function works from the left. I’ve just craved for a function like

RIGHT(A2,FINDRIGHT(“,”,A2,-1))

and it picks the characters from the right counting till it finds the ‘,’!

For instance, “Columbus, OH” is the text in a cell and you need to extract “OH” from the right after the ‘,’ – one way is to find where the comma exists and then find the number of characters from the right by subtracting the total length of string from it… too tedious! But that works…!
Here’s how it looks
= RIGHT(“Columbus, OH”, LEN(“Columbus, OH”) – FIND(“,”,”Columbus, OH”,1))

Now is that all? Have we a universal solution at hand? Or should I say, have I no more problems at hand? Well, as usual, the answer is ‘not really’! What if you had multiple commas in a text string – like “Columbus, OH, United States, 40023″ and you wanted the last part after the ‘,’ i.e. 400023. If we went about working the solution like above, we’ll have Three FINDs to find the location of the last ‘,’!

Worse still?? What if you didn’t know how many commas’s existed in each cell, i.e. if the number of commas in every cell is not fixed! Now that’s a problem and we sure would love to have a function like FINDRIGHT I mentioned above!

What’s interesting is, there indeed is a nice unique solution to this problem, quite innovative I’d say.. great logic!
Here’s the idea – how about if we counted the number of occurrences of the delimiter per cell, and then, temporarily change that delimiter to another delimiter and use that in the FIND function I’ve mentioned above. Voila!!

Step1 :

Find number of occurrences (this is the most important step in all this)

= LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)) (What a great way to get the number of occurrences – very thoughtful!)

Step2:
Substitute the last occurrence only!
= SUBSTITUTE(A1,”,”,”|”, … ) (… follows from the function above which gives the instance to be substituted)

Step3:
Extract using the first formula above
RIGHT(A1, LEN(A1) – FIND(“|”,…,1))

Putting them all together -
=RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,”,”,”|”,LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)))))

And there we have it…

Now this is also not without shortcomings, but we’ll take that up in another post. For now, this is a great tool to use, and just copy that last function and put it in the cell next to where you want to extract!

One great link that helped me with this is given below for reference -
http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba” target=”_blank

Getting filenames of all files in a DIRectory ~

A very elementary request, but this had us searching all over the internet for solutions! We wished to get all the files in a folder, as we had saved multiple data files of Excel in one folder, and went about retrieving data from each file as and when required.

Trying to accomplish this is VBA is one thing, but what if we didn’t need to update it every time, and just needed it this once.. I wouldn’t want to waste my head over this by creating some complex VBA stuff.. then, where do we turn to? DOS!

It’s been a while since I heard that being used actually! But felt quite good trying to work it out.. Anyway, here’s the code to getting the filenames into one text file from a folder.

  1. Go to the file location in DOS using CD <PATH NAME>
  2. DIR /B /O:N > filename.txt
  3. DIR /B /O:N /S > filename.txt to get all files from subfolders as well
  4. DIR /B > filename.txt also works
  5. DIR > filename.txt gets all details other than file name too

Some basic stuff huh?!! But good to know it when you need it!