rowcolor

How to Alternate Row Color in Excel

by with No Comment conditional formattingformattingfunctionsintermediate

Looking at numbers on a plain white spreadsheet can sometimes make it difficult to differentiate one row from another, especially from afar. It can also make the spreadsheet look a bit bland. That’s why in this post I’ll show you how in Excel you can alternate the color of both rows and columns.

The first step is to create a new conditional formatting rule in Excel

Ultimately, all you need to alternate the row color in an Excel spreadsheet is a little bit of conditional formatting. It just comes down to getting the logic and the formula right in ensuring that the correct rows are highlighted.

To get things started, select Conditional Formatting from the Home Tab and then Create New Rule

home tab excel selecting new rule from conditional formatting menu

Once there, you’ll want to select the option to Use a formula to determine which cells to format:

selecting a formula for conditional formatting in excel

This is where we’ll now enter the formula that we’ll want to use for the alternating rows.

Use the MOD function along with ROW to determine which row to apply a different color to

The key function that we’ll need to use is the MOD function. What this function does is it tells us what the remainder is after a number has been divided by a divisor. This is important because what we need the conditional formatting to do is to evaluate each individual row to tell us whether it is an odd or even number, and the MOD function allows us to do that.

For example, the following formula will return a value of 0:

=MOD(6,2)

Since two divided by six will return a result of three and have no remainder, the result of the formula is 0. If, however, we change the formula to this:

=MOD(6,4)

The formula will now return a value of two, since four only goes into six one time, leaving a remainder of two.

Now, it’s simply a matter of applying this logic to each row. To do this, we need to incorporate the ROW function into our formula as well. The end formula is actually not very complex:

=MOD(ROW(),2)=0

The above formula will be true if the row is an even number, and thus, any conditional formatting we have set for that rule will apply. If we wanted to modify every odd row, then the formula could be tweaked as follows:

=MOD(ROW(),2)=1

You could have two sets of rules, one for odd rows and one for evens, but that’s really not necessary. Instead, you can simply select all the rows and then apply the formatting you want for the even rows, and then create a conditional formatting rule for the odd rows. This way, the formatting you apply to the entire sheet will be overwritten by the conditional formatting rules for the odd rows anyway and your original formatting will end up applying only to the even ones.

Applying a different color to the different rows

Once you’ve created your rule, then it’s just a matter of selecting the formatting you want to use and how you want to highlight the rows. I’d suggest a color that is light so that you don’t have too much contrast. This is how my Excel spreadsheet looks after applying a light blue color to every alternate row. I’ve left the default formatting in place for the odd rows.

conditional formatting alternating rows highlighted

If your spreadsheet doesn’t look like this, check to make sure that you have applied the conditional formatting to the entire sheet and that it isn’t only to a select few rows or cells. If you’re unsure about this, refer back to my earlier post on conditional formatting to help give you a better idea of how it works.

You can highlight alternate columns in a different color too

As you may have guessed, the same logic and conditional formatting rules that we used above can be applied to columns as well. Instead of the ROW function we just need to use the COLUMN function in our formula. That’s really the only difference as the formula will look nearly identical:

=MOD(COLUMN(),2)=1

You’ll follow the same steps as far as creating a new conditional formatting rule, but the process is largely the same. The one thing that you’ll notice, however, is that if you have both rules in place, your conditional formatting has now overlapped:

conditional formatting overlap for both columns and rows in blue

In the above example, the formatting is the same color, but if they were different, the overlap would stand out even more. And that’s where it may take some experimenting with different formats to ensure that you get the right overlap and that the different formatting rules blend well together. Assuming, of course, that you want both rules in place.

Other options to alternate the row and column color in Excel

While this post showed you how to change colors for odd and even rows and columns, you can certainly extend that logic even further. For instance, you could decide to highlight every third row by changing the MOD function so that you’re dividing by three rather than two. As long as the logic is sound, you can modify these formulas so that they alternate the rows that you want.

For example, if you wanted to alternate the color of every fifth row and every 13th row on your Excel spreadsheet, you could create one conditional formatting rule to apply to every fifth row and then another for every 13th one as well. Although putting this into one larger formula is possible, it would a bit cleaner to put them into different rules.

When it comes to conditional formatting, there’s a lot of flexibility in how you can structure how your spreadsheet works. However, you also don’t want to get too carried away and make the spreadsheet too colorful and difficult to read. Otherwise, it may end up defeating the purpose and making your spreadsheet less user-friendly.

If you liked this post on How to Alternate Row Color in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

excel-1771393_640

How to Use the New XLOOKUP Function

by with One Comment functionsindexintermediatelookuplookupsmatchvlookup

Forget using VLOOKUP or even INDEX and MATCH, Excel users can now use XLOOKUP!

Knowing how to use VLOOKUP has become almost a basic skill for Excel users. If you’re an intermediate or advanced user, you probably use INDEX & MATCH because of the limitations that are inherent with just using VLOOKUP. Since VLOOKUP can only return values to the right of what the value that you’ve found, it’s a less-than-optimal formula. You can either re-arrange your data, or you can use INDEX & MATCH. It’s a more flexible solution, but it’s also not ideal. After all, you’re now combining multiple Excel functions into one.

Enter: XLOOKUP

XLOOKUP is the solution that Excel users have been looking for…for decades. What the function does is allow you to do what was possible with INDEX & MATCH all in one simple formula.

Let’s go over it with some sample data on the world’s largest cities:

list of the largest cities in the world
Data courtesy of Wikipedia

Doing a regular lookup vs XLOOKUP

Here’s how my formulas would look like if I wanted to return the Country using a value from the City field:

vlookup and index match doing a regular lookup

In the above example, E5 refers to the capital city value. While the INDEX & MATCH combination works, it may not be the easiest for novice users who aren’t comfortable with nesting functions. Here’s how the same calculation would look using XLOOKUP:

xlookup doing a lookup

It’s a much simpler solution. The first argument takes the value you want to look for, followed by the range where you want to search for it, and then the range that you want to extract the corresponding value from. There’s no need to enter a column number the way you do with VLOOKUP, nor is there a need to add another function.

There are optional arguments you can use including how you want to match (see the next section). You can also choose the direction that the lookup goes, in case you don’t want to look in the same order as your data:

xlookup argument to search data

Using wildcards in XLOOKUP

Like with the other functions, you can also incorporate wildcards into XLOOKUP as well. Wildcards work similarly among all three formulas, but the key difference is that XLOOKUP has multiple arguments for its fourth (optional) argument which dictates how you want the data extracted. Entering ‘2’ will tell the function that you want to use a wildcard. Below are the options for the match_mode argument (optional):

  • 0: exact match
  • -1: exact match or next smaller item
  • 1: exact match or next larger item
  • 2: wildcard character match

Here’s a comparison of how you’d get the same result using all three functions using a wildcard:

using wildcards in xlookup vlookup and index match

The logic is the same in the sense that you’ll want to use a wildcard character like * around the term you’re trying to find a match of. In the above example, I used the * around the entire wildcard, and it returned the population for New Delhi in that example.

XLOOKUP here is actually a bit more complicated as with the other functions you didn’t need to specify that you were using a wildcard. Taking out the ‘2’ from the argument would result in XLOOKUP yielding an #N/A error. However, it could be that doing this will make it more efficient.

Finding the closest matches

One of the other options for the matches mentioned above were finding the next smaller or next larger matching items if an exact one wasn’t found. A good example of this is where you’re looking for something like a tax rate where you won’t find every possible income level that someone might enter and you need to ensure that it falls into the correct range.

Here are some sample categories:

sample tax categories and tiers

If I entered an amount of $17,000, it should put me in Tier 3, since that would be the threshold I would have reached under this hierarchy. Here’s an example of how this would be calculated in the three functions:

doing a lookup for tax brackets using xlookup vlookup and index match

All three formulas were able to return the same tier correctly, however, INDEX & MATCH is a bit more cumbersome again due to having multiple functions within it.

The advantage that XLOOKUP has here is that I can select the category that’s either directly below or above the amount I enter, effectively rounding up or down, simply by changing the fourth argument between a ‘1’ (exact or next largest item) to a ‘-1’ (exact or next smallest item).

This is not possible with VLOOKUP, and in order for this to be able to work with INDEX & MATCH, I’d have to change the order from ascending to descending. But what’s impressive is that XLOOKUP is able to find the correct category even if the values are not in any sort of order at all.

Have a look at what happens when I try to completely destroy any sort of hierarchy:

tax brackets sorted into tiers

This is an absolutely dreadful hierarchy that’s not consistent in any way possible. Do the formulas have any chance of getting it right? Here’s how the results looked:

xlookup index and match doing a lookup for next smallest category

Both the INDEX & MATCH as well as the XLOOKUP formulas were looking for the closest matches. INDEX & MATCH returned the lowest tier, which technically was incorrect since $17,000 came in higher than $10,000, which was Tier 2. And XLOOKUP, despite the mess of a hierarchy, was still able to pull out the correct group.

Ultimately, you never want to organize your data in such a horrible way, but this helps demonstrate just how strong XLOOKUP is, to be able to still come out with the correct calculation.

And just for fun, let’s flip the formulas around, this time looking for an exact match or the next largest category:

xlookup index and match doing a lookup for next largest category

There wasn’t a Tier 3 in my incomplete table, but XLOOKUP still found the next largest Tier which was at $25,000 – Tier 5. INDEX & Match found its way into Tier 7.

Creating a dynamic formula

One of the great things about INDEX & MATCH is that you can index an entire database and then dynamically change which column you want to extract from based on a selection and not have to update the range in the formula. For example:

creating a dynamic formula using index match

Why would you want to do this? The beauty of it is that you can change what value you extract based on your selection. Since you’re doing a match, it will look for that field and adjust the column accordingly using the OFFSET function:

You can do this in XLOOKUP as well, and here’s how that formula would compare to index and match:

xlookup index match doing dynamic formulas

The XLOOKUP formula is a bit more complicated as it needs two ranges, and thus, two OFFSET functions are needed. In the INDEX & MATCH combination, only one OFFSET function is needed as it only requires a column number for one of its arguments. Either way, you still need to be familiar with using OFFSET so it’s probably not a dealbreaker if XLOOKUP is a bit longer.

Great, so how do I get XLOOKUP?

There are two things you need to be able to get access to XLOOKUP:

  • Office 365
  • Enrollment in the Office 365 Insiders Program

It’s not an exclusive club or anything, all you have to do is to follow the steps outlined here. By selecting the ‘Insider’ option rather than Monthly, you’ll get more frequent updates and changes. Once you’ve got it set up, then it’s just a matter of waiting for the updates to roll out to you. There’s, unfortunately, no notification, I’d just suggest checking every now and then to see if XLOOKUP shows up in your functions list.

Caveat

One of the things you should remember, however, is that while it may be great to use XLOOKUP, old versions of Excel won’t have access to this flashy new function. And so it’s important to still be familiar with using VLOOKUP and INDEX and MATCH.

If you liked this post on How to Use the New XLOOKUP Function, please give the site a like on Facebook. Also be sure to check out our templates section. You can also follow us on Twitter and YouTube.

Calculating and Tracking Streaks with a Custom Function

by with No Comment functionsoffsetsumvba

With the stocks markets tanking earlier this month, I thought it’d be interesting to track their historical performance and put into perspective just how badly things have been going lately. For those that don’t know, one of my side jobs is writing articles for the Motley Fool Canada and so naturally this example attracted my interest.

However, there’s not an easy way to calculate this in Excel, and so I decided to go the route of a custom function.

What I’m going to be looking to accomplish is a way to to track how many consecutive trading days that a stock has been up or down, and then also calculate the cumulative value of those gains and losses.

If you’d like to follow along with my example, you can download the file I used here (you’ll have to save the file, open it in Excel and enable content, otherwise you’ll see NAME? errors)

Setting Up the Variables


I want the calculation to start from the bottom (the current cell) and work its way back up, since the latest results will be at the bottom. To do this I create a ‘bottom’ variable that looks like this:

————————————————————————————————————–

bottom = selection.Count + selection.Row – 1

————————————————————————————————————–

I want the user to be able to select what range they want the calculation to apply to, rather than selecting everything.

I also setup a variable for the column, which I named as offsetnum:

————————————————————————————————————–

offsetnum = selection.Column

————————————————————————————————————–

These two variables allow me to set my starting point for my calculation.

Determining if I’m Counting Negatives or Positives

The value of the starting cell will determine if I am going to be looking for positive numbers (gains) or negatives (losses), and so I setup an if statement to determine whether the first value is a gain or loss:

————————————————————————————————————–

If Cells(bottom, offsetnum) < 0 Then
    posneg = “negative”
Else
    posneg = “positive”
End If

————————————————————————————————————–

Start counting


The final step involves counting the values depending on whether I’m looking for positives or negatives:

————————————————————————————————————–

For counter = bottom To 1 Step -1

    If posneg = “negative” Then
 
            If Cells(counter, offsetnum) < 0 Then
                streak = streak – 1
            Else
                Exit For
            End If
         
      Else
   
            If Cells(counter, offsetnum) >= 0 Then
                streak = streak + 1
            Else
                Exit For
            End If
         
        End If
     

Next counter

————————————————————————————————————–

My complete function looks as follows:

————————————————————————————————————–

Function streak(selection As Range)

Application.Volatile
Application.Calculate

Dim bottom, offsetnum As Integer
Dim posneg As String

bottom = selection.Count + selection.Row – 1
offsetnum = selection.Column

‘Determine first value
If Cells(bottom, offsetnum) < 0 Then
    posneg = “negative”
Else
    posneg = “positive”
End If

For counter = bottom To 1 Step -1

    If posneg = “negative” Then
 
            If Cells(counter, offsetnum) < 0 Then
                streak = streak – 1
            Else
                Exit For
            End If
         
      Else
   
            If Cells(counter, offsetnum) >= 0 Then
                streak = streak + 1
            Else
                Exit For
            End If
         
        End If
     

Next counter

End Function

————————————————————————————————————–

Calculating consecutive points gains and losses


Now that I have a function to tell me the current winning or losing streak, I can calculate the cumulative gains and losses.
To do this, I am going to sum as far as the streak goes. And so far starters, I’m going to start with the SUM function. I am also going to use the OFFSET function because I need to determine how many rows up I need to add. The OFFSET will start from the current position and determine how far back I need to go to add up the totals in the current streak.

However, because some streaks are negative, I’ll need to also use the ABS function to just grab the number, regardless of if it is positive or negative. My formula looks like this so far:

=SUM(OFFSET(H2,1-ABS(I2), 0

Column H is where my gain or loss value is, while column I is the streak value. Since I want to sum the cumulative gains, I need to reference column H as my starting point.

I added the 1- before the ABS function because that will ensure the number is a negative, meaning that my formula will calculate upward, rather than downward if the number were positive. I also have to decrease the number of cells to offset because I don’t want to include the current cell, otherwise the formula will go too far.

Since I’m not offsetting any columns I set the next argument to 0.

The last argument I need to enter is the height of the offset function, otherwise the formula will just offset by the number specified in the second argument and pull that value, rather than pulling all the values that fall within the range.

This actually involves just copying the same argument again, but this time for the height. My completed formula looks as follows:

=SUM(OFFSET(H2,1-ABS(I2),0,ABS(I2)))

Note

Sometimes with custom functions you might notice that your calculations hang or stop computing correctly. What that means is you just need to recalculate using either F9 or you can edit in the cell and click enter, which will normally trigger a recalculation as well.

Alternatively what you could do is after running the formulas for the dataset, copy them over as values to ensure that they don’t change, since in this case you likely wouldn’t need to recalculate the streak value again.

Loan and Savings Formulas – PV, FV, and PMT

by with No Comment beginnerfunctions

In a previous post I covered my amortization/depreciation template.

Here I will cover how to do present value and future value calculations that are used in that template. I will start with a savings example. Suppose I want to accumulate $500,000 in savings at the end of 25 years after making monthly payments. I will assume an average interest rate of 3%. My inputs will be as follows:

Present Value (current savings) = 0
Future value (target savings) = $500,000
Number of payments (n) = 300 (25 years * 12 monthly payments a year)
Interest Rate (i) = 0.25% (3%/12 months)

Payment  Calculation


To determine the size of the payment I need to be making to ensure I meet my target calculation, I will need to use the PMT (payment) formula. With the above inputs, my formula will look as follows:

=-PMT(0.0025,300,0,500000)

I enter a negative before the formula to ensure my value will be positive. This yields a result of $1,121.06. I could add an additional argument to say that the payments are at the beginning of the period as opposed to the end. All I need to do is add a another argument with the number 1, as shown below:

=-PMT(0.0025,300,0,500000,1)

The result would be $1,118.26; a difference of less than $3 a payment.

Future Value Calculation


I can test my calculations by now doing a future value calculation. My inputs remain the same, except now I have a payment amount.  The future value formula will look as follows:

=FV(0.0025,300,-1121.06,0)

I have made the payment amount negative so that the formula results in a positive number. My future value equals 500,001.53, confirming that I will reach the target amount with this payment amount. I could also change the payment number from 300 to 150, to determine how much I will have amassed halfway:

=FV(0.0025,150,-1121.06,0)

This tells me I will have a balance of $203,723.81 after 150 payments.

I’ll switch over to another example now. Let’s assume you have a mortgage and want to know what your balance is today. You can use a similar calculation, except this time you will have a negative present value and don’t know your future value (today’s value). Suppose a mortgage of $250,000, a 30 year mortgage with monthly payments (n=360), an interest rate of 5% and payments of $1,342.05 (this can be calculated in much the same way as the payment calculation was done for the first example).

My inputs are as follows (assume I want to know the balance halfway through the mortgage, after payment 180):

Present Value (mortgage amount): $250,000
Number of Payments (n) = 180 (15 years * 12 monthly payments a year)
Interest Rate (i) = 0.4167% (5%/12 months)
Payment = $1,342.05

The formula is as follows:

=FV(0.004167,180,1342.05,-250000)

Again you will notice the present value amount is negative here. This is because this is the amount owing. If this was the same sign as the payment amount the balance would increase rather than decrease. This calculation tells my the mortgage balance after 180 payments, or halfway through the mortgage would be $169,709.77. You may notice slightly different amounts because of the interest rate you use. In the above example I rounded to 0.004167 however if you reference the cell that has the interest rate calculation rather than a hard-coded number you will get a more accurate result.

Present Value Calculation


I will move on now to a present value calculation. In this example, I want to determine what mortgage amount can be afforded based on a specific monthly payment. Suppose I want the monthly payment to be $2,000; the term to again be 30 years; the interest rate to still be 5%. With these inputs I can determine what mortgage amount I can afford based on those assumptions. My formula will be as follows:

=PV(0.004167,360,-2000,0)

In the formula above I again set the payment amount to a negative so that the formula gives me a positive number. The result is a value of $372,563.23.

When doing these calculations you are always better off referencing formulas for interest rate calculation as opposed to hard-coded numbers. As you will notice, even a slight difference in the interest rate can have a big impact on your result, especially when dealing with a large number of payments. I have hard-coded examples in all of the examples here only for illustrative purposes but in practice I would recommend avoiding hard-coding an interest rate.

date difference intervals

Calculating Date Differences Using VBA

by with No Comment datesfunctionsvba

In VBA there is a custom DateDiff function that allows you to easily calculate the difference between dates – whether you want the difference to be in days, weeks, months, years, it is easier to accomplish this in VBA than through regular Excel formulas. I have piggy-backed off the DateDiff function to make a custom function in Excel that makes it easy to use as a formula in your spreadsheet.

The custom function I have created is called datecalculation and consists of three arguments: start date, end date, and interval. The interval determines how the difference is calculated. The interval needs to be in quotations and use one of the following codes:

date difference intervals

Below is an example of how the function work when computing the difference between January 1, 2016 (cell C2) and January 1, 2017 (cell C3). The interval codes are in column A.The result column is the date difference according to the selected interval.

vba date difference function

To make this function work in your spreadsheet simply insert the following VBA code. If you are not sure how to do that, please refer to this post, specifically the section about inserting VBA code.
————————————————————————————————————————
Function datecalculation(date1 As Date, date2 As Date, interval As String)
datecalculation = DateDiff(interval, date1, date2)
End Function
————————————————————————————————————————

Custom Function That Does Multiple Lookups

by with No Comment functionslookupvba
MatchThis function
This function works as if you were doing multiple lookup statements in one formula. The example I’m going to use is if you wanted to look at your credit card statement and from the description determine what vendor it is.
For this function to work I need to create a named range of all the values (e.g. possible vendors) I want to cycle through to compare the string (e.g. credit card data) against. The named range needs to be called LookupList. Below you will see the LookupList I created. (For more on named ranges, see this post)
I’ve added a header but that is not necessary. As long as the list is a named range called LookupList. The adjacent column is the value that will be returned if the value in the LookupList is found. You need to ensure this column is also filled in or else the result of the formula will be blank, regardless if there is a match.
When I run the custom function, the function will cycle through the LookupList from top to bottom to see if one of those values is in the cell I am using the formula on and if so, return the related result. For that reason, the LookupList also needs to be in descending order, to avoid a premature match (e.g. finding Store A before the function finds Store ABC)
Column A is an example of data from a credit card or other source that may have various characters before and after what you are looking for. You could use the MID function to extract that data but that will only work if that data is consistently arranged the same way. It might be, but using this function it won’t matter and it will just look if any of the values in the LookupList are contained in the string, regardless any other characters before and after.
Column B is the MatchThis function. And since the LookupList is already defined the only argument is the data that you want to look at, which in this case is column A. In column B2 the formula is simply =matchthis(A2). Because it matches Store A, it returns the value A (from the results column).
Below is the code for this function:
————————————————————————————————————————————–
Function MatchThis(matchcell As Range)
Application.Volatile
Application.Calculate

Dim LookupList As Range
Dim c As Range
‘Identify the range of cells you want to compare against. The lookuplist is what will be compared against and the column to the right of it will be the output
Set LookupList = Range(“LookupList”)
‘Go through each of the cells looking for the criteria in cell c, and if it matches, pull the value from the next column
For Each c In LookupList
If InStr(1, matchcell, c, vbTextCompare) > 0 Then
MatchThis = c.Offset(0, 1)
Exit Function
End If

Next c

End Function
————————————————————————————————————————————–

Custom Function: Extract All Matching Data

by with No Comment advancedfunctionsvba

The purpose of this function is to act as multiple vlookups to pull all data that has multiple matches, and allows the user to specify how they want the data delimited

The function is called EXTRACTALL and it has four arguments:
Argument 1: Looup Field. This is the value that you are looking for. If it is text, make sure it is in quotes
Argument 2: Lookup Range. The range the function is going to search to find the value specified in argument 1
Argument 3: Output Range. If a match is found in the lookup range, this is what will be returned. The ranges should be the same length
Argument 4: Separator. This is how the results will be separated.

An example of the formula is below:

=EXTRACTALL(“A”, B:B,A:A,”,”)

This will look for the letter A in column B, and when there is a match the value from column A will be pulled. All values will be separated by commas.

You will note the values that correspond to A’s are 1, 3, 5, 8, and 9 which is what the result displays in cell D11. This acts effectively the same as a lookup function might with the difference being this will pull every match and put it into one cell whereas a lookup will just grab the first match and nothing else. This is more useful in the case of text values because after using this formula (especially if a comma separator is used) you can convert from a comma delimited field into multiple fields.

Below is the code:

Function extractall(lookupfield As String, lookuprange As Range, outputrange As Range, separator As String)

Dim cl As Range
Dim counter, offsetcol As Integer

counter = 0
extractall = “”


offsetcol = outputrange.Column – lookuprange.Column


For Each cl In lookuprange

    If cl = lookupfield Then
        counter = counter + 1

            If counter > 1 Then

               extractall = extractall & separator & cl.offset(0, offsetcol)
            ElseIf counter = 1 Then

                extractall = cl.offset(0, offsetcol)
            Else

            End If
    End If

Next cl

End Function

Find the xth occurrence of a day in a month

by with No Comment advanceddatesfunctionsvba

This function is called dayx. It consists of the following arguments: occurrence number, day of the week, month, year. For example:

dayx(2,”Monday”,10,2014)  or dayx(2,1,10,2014) will return the second Monday of October 2014, which is October 13, 2014

For information on how to insert a custom function, see this post.

Here is the code for this function:
———————————————————————————————————————–

Function dayx(xoccurrence As Long, xday As String, xmonth As Long, xyear As Long)


Dim firstweekdayofmonth As Long
Dim currentmonth, endofmonth, firstdayofmonth As String


Select Case UCase(xday)
    Case Is = “MONDAY”
        xday = 1
    Case Is = “TUESDAY”
        xday = 2
    Case Is = “WEDNESDAY”
        xday = 3
    Case Is = “THURSDAY”
        xday = 4
    Case Is = “FRIDAY”
        xday = 5
    Case Is = “SATURDAY”
        xday = 6
    Case Is = “SUNDAY”
        xday = 7
End Select

‘Convert month number to name
currentmonth = Format(DateAdd(“m”, Val(xmonth) – 1, “January 1”), “mmmm”)
endofmonth = Format(WorksheetFunction.EoMonth(Format(DateAdd(“m”, Val(xmonth) – 1, “January 1”), “mmm dd, yyyy”), 0), “mmmm dd, yyyy”)

‘Determine the first day of the month
firstdayofmonth = Weekday(currentmonth & ” 1, ” & xyear, vbMonday)

‘Calculate
If xday >= firstdayofmonth Then
dayx = Format(currentmonth & ”  ” & (7 * (xoccurrence – 1)) + 1 – (firstdayofmonth – xday) & ” , ” & xyear, “mmmm d, yyyy”, vbMonday)
Else
dayx = Format(currentmonth & ”  ” & 7 + (7 * (xoccurrence – 1)) + 1 – (firstdayofmonth – xday) & ” , ” & xyear, “mmmm d, yyyy”, vbMonday)
End If

dayx = Format(DateAdd(“m”, 0, dayx), “mmmm dd, yyyy”)

End Function

———————————————————————————————————————–

Using Custom Functions and Macros

by with No Comment advancedfunctionsvba
If you come across a custom function or macro that you want to use in your spreadsheets, this post will show you how to do so.

As far as coding goes, they key difference in a function as opposed to a sub procedure in Excel is in the way the code is executed. In a function, you enter in just like you would any other function, by typing out the name of the function and entering in any required arguments. With a sub procedure, it is typically executed via an event, a button, or shortcut key. Also with a function, the user will manually enter values for key variables, whereas for a sub procedure in most cases those will be calculated within the procedure itself.

I’ll demonstrate how a custom function works and how to copy it into your code.
Below is a basic function that will multiply a value by 5:
__________________________________________________________________
Function times5(multiply As Integer)
times5 = multiply * 5
End Function
__________________________________________________________________

If you came across this code and wanted to insert it into your spreadsheet, what you would do is open VBA (alt + F11). Once inside VBA, click on Insert and select Module


Now paste the code into that module
And that’s all you would need to do before you can start using a custom function. Now if I go back into a worksheet, I can begin using the code by using the name of the function in my formula.

 There is only one argument in my function that I have to enter, that is for the multiply variable. Inside the function times5 there is one variable declared (multiply). The formula multiplies the variable by 5 to arrive at the result. If I had multiple variables (e.g.if the first line read Function times5(multiply as integer, multiply2 as integer) then that would signify two variables that need to be identified) then I would need to enter more than one argument, the way you would for any other function in Excel that has multiple arguments. But in this case I only have one variable.
Now as you will see, the result is the multiply variable multiplied by 5.
I can access this function for any worksheet within this workbook. If you want to copy a custom function to your spreadsheet, follow the above steps and then you can utilize that function in your worksheets. 
The one downside of custom functions is that the tool tips that normally show up in regular Excel formulas telling you what arguments are required are not available and so you need to make a note of what arguments are required for a function.

Inserting a Sub Procedure (Macro)

If instead of a custom function you had sub procedure (macro) you wanted to copy into your code, the steps would be the same, except instead of using a formula to execute the code, you would need to assign either a shortcut key or a button. 

IF Functions: Calculating Data If Criteria Is Met

by with No Comment functionsintermediate
An overview of how various IF functions work

IF Function
An if function creates a test, and can have different results depending on whether the test is true or false.

Example 1

 =IF(A1=”A”,1,0)
In this example, if A1 is equal to the letter A then the result of the formula is 1. If it is equal to B, C, or anything else, the result will be 0. It will look for an exact match – if you don’t want an exact match, you’ll need to use wildcards (see later down).
You can make nested If functions as well – instead of putting  a 0 in the formula above you can add another if function:
Example 2

=IF(A1=”A”,1,IF(A1=”B”,2,0))
In this formula, if the cell A1 does not match A, then it will do another test. The second test is checking to see if it matches B – if it does, it will assign a value of 2, and if not, a value of 0. You can keep adding If functions as much as you need to. But keep track of the parentheses to make sure they are properly closed. If you find yourself having to do many nested if functions then you may be better off using a lookup formula.
COUNTIF Function
The COUNTIF formula counts the number of items your criteria is met. See the example below:
Example 3
=COUNTIF(A1:A7,”A”)
This formula is looking at all the cells in the range A1:A7 and counting them if the match the letter A. This would result in 5. 
In newer versions of Excel, there is a plural version of COUNTIF– COUNTIFS. This allows you to use multiple criteria:

Example 4

=COUNTIFS(A1:A7,”A”,B1:B7,1)
This formula builds on the last, where it looks for the value A in cells A1:A7 and then also looks for the number 1 in cells B1:B7. This would result in 2. As there are two instances where A and 1 are in the same row. 
Note: If your ranges are not identical (e.g. A1:A7 and B1:B6), your formula will not work properly. Also, because in this example the formula was looking for a number, it does not have to be in quotations. You can keep adding more criteria by adding another comma instead of closing the formula.
SUMIF
SUMIF function works similarly to COUNTIF, the difference being is it can sum a separate range:
Example 5

=SUMIF(A1:A7,”A”,B1:B7)
In this formula, it will add all the values in B1:B7 when the corresponding values in A1:A7 are equal to A. This would result in a value of 10 (1 + 1 + 2 + 3 + 3). Like the COUNTIF function, SUMIF also has a plural version SUMIFS that can be used for more than one criteria.
There is a minor difference in how SUMIFS works:

Example 6

=SUMIFS(C1:C7,A1:A7,”A”,B1:B7,1)
In this formula, the values in C1:C7 will be added when the corresponding values in A1:A7 are equal to A and the values in B1:B7 are equal to 1. The key difference here is you select the range you want to sum first, and then the criteria comes after. In the SUMIF function, the criteria range came first, and then the range to sum came after. Similarly to the COUNTIF function, you can keep adding criteria with additional commas.
AVERAGEIF
This function works the same was as sumif, the main difference is instead of summing everything, it will take the average:
Example 7

=AVERAGEIF(A1:A7,”A”,B1:B7)
This formula will take the average of all the cells in the range B1:B7 where the related values in A1:A7 equal A. Similar to the way SUMIFS works, so too does AVERAGEIFS.
WILDCARDS
The above examples worked well if you wanted to match exactly what was in the cell. But what if you needed only to match one word or letter, or a portion? That is where wildcards become useful.
The first way to use a wildcard, is by using an asterisk (*). An asterisk represents unknown values before or after. For example:
Example 8
=COUNTIF(A19:A28,”P*”)
In this example, it will count the names that start with the letter P. Because the criteria start with P and is followed by an asterisk, what this means is that the first letter has to be a P, and the rest can be anything (three cells match this criteria)
Similarly:
Example 9

=COUNTIF(A19:A28, “*P”)
This formula works the opposite way, in that it will count the cells where P is the last letter, and any values can come before (no cells meet this criteria).
And also:
Example 10

=COUNTIF(A19:A28, “*P*”)
This formula will count the cells where P is anywhere in the cell (the same three cells that matched Example 8 are the only ones true here as well).
Note: the criteria here is not case sensitive.
But what if you are only looking for a certain number of characters? For example:
Example 11

=COUNTIF(A19:A28,”*H???”)
In this formula, it will count names that end with the letter h and 3 characters after. The ? represents one character (cannot be a number). This allows you to control your result to a finite length, whereas the asterisk doesn’t limit the number of characters. The cells that meet this criteria are A21 and A27.
A similar formula:
Example 12
=COUNTIF(A19:A28,”E??c*”)

This formula will look in the range and count the cells that start with the letter e, have any two letters after that, then a c, and anything else after that. The only result in the list is cell A24.