Category Archives: Excel Formulae

Converting Monthly data to Weekly data in Excel

A lot of the time, there are differences between different pieces of data received from a client/ data team – one being when different pieces roll up to different levels in a nested structure, for eg. weekly vs monthly vs yearly. Is that problem? Let’s take a look..

Taking the example here – let’s assume we have Monthly data for spends for campaigns while we’re working at a weekly level with Sales. Thus, we’re forced to split Monthly figures into the Weekly format to be used for any purposes – from reporting to modeling. It is often required to accurately split the monthly into weekly, so as not to double count or misrepresent the spends in the weekly format. One of the biggest problems – overlapping days between the week and months.

For eg. from the image below – the week of 27th Jan 2014 contains days from Jan ’14 as well as Feb ’14. In order to accurately get the spends for this week, we need to factor Jan & Feb spends weighted by number of days.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

 

 

 

Credits to this guy – “AlphaFrog”. His post (http://www.mrexcel.com/forum/excel-questions/627101-converting-data-monthly-weekly.html) provided the basis for this post. Below is a quick snapshot from his page showing the flow of logic.Example - Blog

Advertisements

Round “time” in Excel

How does the Time format work in Excel?
– 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”

What kind of functions would we need to run on Time formatted data?

Here’s 2 examples –
– Rounding time to the nearest hour, half hour or quarter hour (for eg. converting running time into buckets of 15 min intervals)
– Finding the difference in time between one day and the next (How many hours has elapsed between onset time and end time?)

Below is a set of syntax to round time to the nearest 15 min window.

= (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.

Extracting Text from Right of String in Excel

When working with Strings in Excel, it doesn’t get any easier than this. You can practically do anything you want with the strings and also visualize as you do them.

Some Functions that are frequently used – LEFT(), RGHT(), MID(), FIND(), SUBSTITUTE(), TRIM(). These are even more powerful when used in combination. LEFT() & RIGHT() are used to extract a part of the text from LEFT/RIGHT of the string input. MID() is used to extract text from anywhere in a string given the start and end positions. FIND() is used to locate a character or string in a string. SUBSTITUTE() is used to substitute a character or string within a string. TRIM() is used to trim any trailing spaces.

So, what’s the BIG deal with these functions anyways?

Consider extracting the First Name from every record in the file – assuming every name is stores <First Name> <Last Name>. There are a number of ways to do this – I’d use a combination of LEFT and FIND. Here’s a quick syntax – LEFT(A2,FIND(A2,” “,1)-1)

Perfect. That was easy. Now what?

What if I wanted to extract the Last Name of every Record?

While the RIGHT() function certainly extracts text from the right, when used with FIND() – things get a little complicated. So, RIGHT(A2,FIND(A2,” “,1)-1) does not work!

But this does – RIGHT(A2,LEN(A2) – FIND(A2,” “,1) + 1)

Great! We’re set here. Is there any more to this?

What if we had First, Middle & Last Name for every record? What then? How will you extract the last name in this case? Or, let’s take the case of Addresses – can you get just the PINCODE for every address in the database?

What this entails is – nested FIND() to locate the correct delimiter. For example, in the case of First, Middle & Last name, we’d have to locate the second ” ” (space) for the start number for Last name. Here’s the syntax – RIGHT(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1),LEN(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1))-FIND(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1),” “,1) +1)

Too complicated? What if we have more than 2 delimiters? What if we don’t know how many exist?

It surely doesn’t have to be so complicated. Why can’t FIND() just look from the right of a string?!

There indeed is a neat solution to this.
Here’s the ideaHow about if we counted the number of occurrences of the delimiter per string, and then, temporarily change that delimiter to another delimiter and use that in the FIND function I’ve mentioned above. Voila!!

Still confused?? Take a look below for a step by step explanation –

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… This can be further improved using the ‘INSTANCE’ option in SUBSTITUTE() function.

This should handle most of the situations that calls for text extraction directly from the right. Just copy that last function and put it in the cell next to where you want to extract (ofcourse, redirect the A1 to your cell) and you’re good to go.

Don’t have to thank for this one! ūüôā

Removing Duplicates in Excel 2003 or earlier

Filtering out the distinct values of a Column/Variable/Range is a very common need when working with Data. Prior to Excel 2007, there was no in-built functionality in Excel that could easily remove duplicates from single/multiple column.

Excel 2007 & Later – The Remove Duplicates in the Data tab does this with the click of a button.

What if you’re using Excel 2003 or earlier? There are a variety of ways how you can achieve this – starting with the advanced filter, moving to more complicated but easy to replicate methods.

The Advanced Filter

Advanced FilterThe Advanced Filter (from the Data Menu) is the in-built mechanism in Excel to filter out the unique cell values of a variable/column/range. Remember to check the “Unique Records only” check box.

Using this, you have two options,

  • Filter in-place: This option leads to filtering of the range in the¬†current location. Loss of data is inevitable, unless you have a copy of it
  • Copy to another location: This is the more useful option, which helps transport your list of unique values to the desired location

Enter the location of the range to be filtered in “List Range”, and the location of destination in “Copy To”. The “Criteria Range” is used to filter out any desired/undesired criteria, in other words, filtering out the condition required. Filtering using a Criteria has many variants to it, this link provides more context (http://www.contextures.com/xladvfilter01.html).

While the Advanced Filter is a great way to filter, if you need to filter more than once, it can get quite painful. Some of these methods below should come to your rescue –

  1. Using Arrays – The following link (http://spreadsheetpage.com/index.php/eee/issue_no_4_april_30_1999/) explains this method in the “Power Formula Technique”. It basically consists of three arrays,¬† Thelist: This consists of the expanding list of values to be filtered; sArray: This array consists of the locations of the unique values; and finally, TheUniqueList:Code Snippet for Array Filtering
  2. Using Range.RemoveDuplicates & Range.SpecialCells(xlCellTypeBlanks).Delete in VBA: 
  3. Using the Collections Object in VBA: This method is the fastest method as far as comparison in terms of speed goes. Refer to this page for a ready piece of code, that runs like a darling! (http://www.dailydoseofexcel.com/archives/2008/12/11/create-unique-list-from-selected-cells/).
  4. Using the Dictionary Object in VBA: Dictionary is very similar to Collections in being able to store & handle related data as groups. The advantage of Dictionary over Collections being the use of “KEY” property.
  5. Using the 1D Array Options (split, join, filter): This is a very innovative way of executing the filter, and this was something very new to me too. Look it up in the link provided with the Collections Method.