How do I use a formula to count the remaining days until the end of the month in Excel?

Using the =DAY(TODAY()) commands I can get the date 2/15/2010 to show that we are on day 15 of the month. I would like the number all the way up to the last day of the month and then stay there. In this example November counted up to 30 days, Dec to 31, Jan to 31, and since today is the 15^th of Feb it is currently on 15. Tomorrow it will be 66. After the 31 I would like it to stop at 31. Is there a possible formula for this?

Month	Days
2009-Nov	30
2009-Dec	31
2010-Jan	25
2010-Feb	15

Yes there is a formula. First, you would need to have a fixed component or refer to a year/month value. Try this for January 2010:

=IF(--TEXT(TODAY(),"yyyymm")>201001,DAY(DATE(2010,2,0)),DAY(TODAY()))

That formula would begin by returning 1 on 01-JAN-2010 and increment by 1
each day up through 31-JAN-2010...then it would stay at 31

February 2010 would be this:

=IF(--TEXT(TODAY(),"yyyymm")>201002,DAY(DATE(2010,3,0)),DAY(TODAY()))

Now, one other thing you can do is take the 201002 numbers and place those in a separate column and then just point to that column in your formulas.

What do the hypens before TEXT do? A leading hyphen causes Microsoft Excel to interpret the contents of a cell as a formula. If you want to use a leading hyphen but not create a formula, use an en dash (option-hyphen) or an em dash (shift-option-hyphen) in place of the hyphen. Excel treats them like any letter or number.

You can also force Excel to treat the contents of any cell as text by typing an apostrophe at the beginning of the cell contents. Alternatively, format the cell as text (choose Cells from the Format menu, click the Number tab, and double-click Text in the category list). After changing a cell to text format, you may have to press command-U to make the change take effect.

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.

How do I create a week ending date formula for an expense report?

Let’s say you are putting together an expense report sheet for all of your employees to use. You want them to be able to enter a week ending date and the spreadsheet will automatically compute the rest of the dates for the week. Here are the two main formulas we will be using:

=IF(B1<>"",IF(WEEKDAY(B1)<>7,B1+7-WEEKDAY(B1),B1),"")

=IF($B$2<>"",$B$2-6,"")

First it says, if the date in cell B1 does not equal (< >) blank (“”), then go on to the next part of the formula, otherwise leave blank. The formula =weekday() returns a number 1 to 7 identifying the day of the week of date. In our example, I have chosen 2/12/2010 as the week ending date. =weekday(B1) turns out to be 6. So, if the weekday of B1, which is 6, does not equal 7, then take the value of B1, 12, add 7 to get 19, then subtract weekday of B1, 6. We end up at 13. (recap: 12+7=19. 19-6=13.) The date displayed is 2/13/2010.

Now it is on to the next formulas for each day. Sunday’s formula is: =IF($B$2<>"",$B$2-6,""). If B2 does not equal blank then subtract 6, otherwise leave blank. In our example we have 13-6=7 which gives us the correct date for Sunday, February 7, 2010. The formula for Monday is: =IF($B$2<>"",$B$2-5,"") which gives us 13-7=8 for Feb. 8th and so on until Saturday. There you have it, one easy way to improve your expense reports.

I could just put this up as templates and not even bother explaining how they work but how many people would take the time to try and figure out how they work? You will learn a lot and feel much more accomplished if you try building these spreadsheets on your own. Feel free to let me know if you have any questions or comments.

How do I highlight duplicate rows in Excel without deleting them?

There is a Delete Duplicates function in Excel that makes it very easy to delete any duplicate data in your spreadsheet. However, sometimes you may not want to delete that data, you just want to know that there are duplicates. One way of doing this is with a simple macro. To highlight and draw attention to duplicates in a Microsoft Excel spreadsheet with a bold red color create this macro (alt+F11 opens the macro editor):

Sub DupsinRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".

How do I create nested IFs formula or an IF in conjection with OR function?

IF/OR

To create a Microsoft Excel formula with an IF and an OR statement read the following example:

=IF(OR(E15=“ALUMINUM 2117”,E15=“STEEL ALLOY”), ”CADIUM PLATE”, ””)

If the text of cell E15 is exactly “ALUMINUM 2117” OR “STEEL ALLOY”, then the selected cell will display “CADIUM PLATE”, otherwise no value will be displayed because of the null set, or empty quotations at the end.

Nested IFs

You may need a formula that involves several IF statements in one. We call these IFs inside IFs Nested IFs (that's a lot of IF's!) See these examples:


=IF(C2=2,0.062, IF(C2=3, 0.094, IF(C2=4, 0.125, "")))

Explanation: If the value in cell C2 is equal to 2, then the dimension 0.062 will be displayed, if the cell C2 is equal to 3, 0.094 will be displayed, if cell C2 is 4, then 0.125 will be displayed, otherwise nothing will be displayed.
Make sure the number of left parentheses equals the number of right parentheses (color coded in Excel). Also note that Microsoft Excel 97-03 only allows users to have 7 levels of nested Ifs.

Other examples:

=IF(U17="CRES 303", "C", IF(U17="CRES A286", "CA", IF(U17="ALLOY STEEL", "-", "")))

=IF(C2=2,0.062, IF(C2=3, 0.094, IF(C2=4, 0.125, IF(C2=5, 0.156, IF(C2=6, 0.187, IF(C2=8, 0.25, ""))))))

Stay tuned for more!

Please feel free to ask any questions.

How do I copy a range with an Excel macro? (And over Range macros)

To copy data from a specific range can be done with the following macro in Microsoft Excel 2007. In this example, data is copied from the current sheet to the active cell.

Sub CopyRange()
Range("A1:A3").Copy Destination:=ActiveCell
End Sub

To copy from a range in another sheet, Sheet4 in this case, to the active cell you need to change the code to:

Sheets("sheet4").Range("A1:A3").Copy Destination:=ActiveCell

To specify a macro to go to a specific range you can use the Goto method. Here I have already named a range in my worksheet called "Engineering". You may also use an alternative method like the Range select method. Naming a range in Excel is recommended rather than specifying an absolute cell reference.

Sub GoHere()
Application.Goto Reference:="Engineering" OR Range("Engineering").Select
End Sub

Assigning range names to a range of cells.

Sub RngName()
Selection.Name = "myRange"
End Sub

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007?

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007? The formulas you will need use are the INT, MOD, COUNTA, ROW, and OFFSET functions. Here is an explanation of each:

INT rounds a number down to the nearest integer.
MOD Returns the remainder from division: MOD(number,divisor).
COUNTA function counts the number of cells that are not empty in a range.
ROW(reference) is the cell or range of cells for which you want the row number.
Row ( ) If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.
Example: =ROW( ) - 1 inserted into a cell in row 26 will yield the result 25.


Here are the formulas for a two column combination:
=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)
=OFFSET($A$1,INT(MOD((ROW()-1),COUNTA(B:B))),1)

First Column Formula Explanation:

=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)

Takes the row number and subtracts one from it. Then it divides this number by the number of options in the second column. This value is then rounded down to the nearest integer (whole number). This in turn is the number of the row in which it will select the answer, starting with 0.




Later on I will give an example of to use this formula up to ten unique columns.

Excel Spreadsheet Template Example: Track Record

One of the activities that I love to do is to travel to amusement parks across the United States and ride roller coasters. I also love looking at numbers and data. I decided to combine the two using Excel. I have created a track record spreadsheet, a list of all of the roller coaster I have ridden along with several statistics I was interested in, such as height, speed, length, and inversions. This is my explanation of how I made it. Your spreadsheet doesn’t necessarily have to be for roller coasters, it could be for any other hobbies or interests that you have that you want to look at stats for.

The first thing I did was list all of the amusement parks I have visited that I could remember. This is placed on a sheet in the workbook that I called the “Master List” because, well, it contains all of the master lists used later on. In the next column I listed all fifty states. The next category is type of coaster- wood or steel. I also decided to make up a class type and finally, I listed all the roller coaster manufacturers I came across while looking up data on rcdb.com.

The next sheet of the workbook is where all of the individual coasters that I have ridden are listed. The first column is the name of the coaster, inputted by me. Under column B is listed the park where the coaster resides. Now for these entries you simply have to pick the name of the park on a drop down list which is generated from the first sheet. The same goes for the state, type, class, and manufacturer. I use drop down lists so you don’t have to do a lot of typing or copy and pasting. It reduces possible error which makes the final data more accurate.

How do you create the drop down lists in Excel? You simply use the name manager to name your lists and then use data validation to actually change the cell from default to a list. Here are two quick pictures. If you need further explanation go here.

The next section is all of the data I wanted to look at. This is all from rcdb.com. Categories I were interested in and available were length, height, drop, speed, inversions, year opened and year closed. As you can see there are a few holes but that is ok because those  are mostly on the smaller, kiddie rides. The last section is where the user needs to input their own data. I decided to list the number of times I have been on the ride, when my first ride was, and the last year it was that I rode. This section isn’t necessarily but I thought it would be fun to look at.

Now it’s time to take a look at the data, which I placed on the third sheet.  In the first column I have called up the list of visited amusement parks from the Master List tab by using the formula =’Master Lists’!B2. The next column I have is to compute the number of roller coasters I have been on at each amusement park. I used this formula:
=IF(COUNTIF(Data!B:B, Stats!A2)=0, “”, COUNTIF(Data!B:B, Stats!A2))
which basically looks up the park listed in the first column, for example “Holiday World” and it goes through the column B in the Data sheet (the park column) and counts how many roller coasters are associated with that park, Holiday World. In this case, I have been on four roller coasters at Holiday World (Voyage, Raven, Legend, and Howler).

As you can see, Cedar Point is the park I have visited with the most coasters, 18 (I counted Gemini as two separate rides. Some would argue this point but when you make up your own track record you make up your own rules!) You can also see I visited Legoland California but did not ride any of the coasters. They looked far too scary!

I used the same formulas for the next section, the states. I’ve been on coasters in ten different states. Ohio had by far the most coasters with 44, which should also come as no surprise since I lived there for 23 years and it had three great parks. Florida comes in second with 18 and PA close behind with 16. The total number of coasters is 116. This is a good place to double check your work. If your total number comes out the same here as in the first section everything is fine, but if your totals don’t match up then there is a problem somewhere. Go back and double check all of your formulas. Next is the type of coaster. 70% of the coaster I have ridden have been steel versus 30% wood structure.
Next is our classes and manufacturer’s data. I’ve been on 20 roller coaster built by Arrow Dynamics, which is no surprise because they were THE coaster company for a good number of years. Next is Vekoma and B&M.

Now we get to the actual statistics. By listing all of the lengths for each coaster I can come up with an average, max, and min. These formulas are quite simple: =AVERAGE(Data!G3:G201), =MAX(Data!G3:G201), =MIN(Data!G3:G201).
The average length of a coaster is 2964 feet, height is 107, drop is 122.7’, speed is 53.2, and goes upside down one time.  I’ve traveled over 54 miles on different roller coasters! The Beast was the longest coaster and Top Thrill Dragster was the tallest and fastest. Any guesses as to what was the shortest?

Another interesting stat to look at is average age versus average life of a coaster. The average life of a coaster is how long it actually exists. To get this number I took the average year a coaster was closed and subtracted the average year opened to get 17. This is only for rides that have actually closed and been torn down for good. The average age is this year minus  the average year opened. The average age of coasters I have ridden is 20. When one of these rides closes it will bring up the average life statistic. The oldest coaster I rode was the Big Dipper at the now defunct Geauga Lake.

One final  section to look at is the number of new rides I’ve visited each year.  The formula I used is =COUNTIF(Data!P:P, V23) which counts the number of rides  if the years match up. As you can see, 2006 was a great year with 24 new rides. The past two years have been a little disappointing in that regard but here’s hoping for a great 2010!

What other data would you like to see looked at or examined? Please share your thoughts or suggestions!

For more roller coasters be sure to check out Coaster101!