The Task
Create a tool for comparing months using a weather index using spreadsheet software such as Microsoft Excel.Use a weather index such as one created for a previous mental exercise or the weather index I created. Develop a spreadsheet that uses a score for each month and can be used to compare each month with the same month from previous years.
My Solution
I used a separate spreadsheet. This prevents Excel from recalculating too much and isolates the work done on this exercise from work done on the previous exercise.I decided to use VLOOKUP to convert the table in the original spreadsheet into a table sorted by month. This means that I have to have values to look up. Each row starts with a basic date value. To keep my data clean, I have decided to use the first of each month. I also used a trick to hide the date. I am using a custom date format of just space. The reason I did this was because my formulas rely on these values, but the spreadsheet looks better without it.
I needed to figure out which month and year each date value represents. Since Excel would return a number for month, I created a table, tblMonths, in the Tables sheet. In that table, the first column shows the numerical value for the month and the second column shows the text I want to use. The following formula simply extracts the month from the date value and uses the table to convert the numerical representation of the month into text.
=VLOOKUP(MONTH(A2),tblMonths,2)
Extracting the year is even simpler since the numerical representation of a numerical year should be acceptable.
=YEAR(A2)
The next 31 columns are dedicated to the scores for each individual date. I started by figuring out a formula to establish the date value according to the day of the month from the column header along with the month and year from the column.
DATE($C2,MONTH($A2),D$1)
The final score for each day is in the lucky 13th column in the table from the original spreadsheet. The simplest way to reference another workbook is to have that other workbook open while working on the new. This way, you can just select the data you want to use while you are editing the formula.
=VLOOKUP(DATE($C2,MONTH($A2),D$1),'[Washingtonian Weather Index - Dates.xlsx]Daily'!$A:$M,13,FALSE)
The reference will break the spreadsheet when you look at it, so I have removed the formula for the individual dates. I have copied and pasted values to replace the formula with the score that was generated for each date.
I did not take the most formula-intensive approach to addressing the variable lengths of months. I deleted the empty dates at the beginning of June 1948. The data has been set up as a table, so I filtered by month. I deleted the formula from the 31st of every month that has 30 days. The 30th and 31st for every February was also deleted, and I deleted the 29th for the years that weren't leap years. I now delete the extra days of the month as I enter them.
That just leaves the averages. Without anything entered in days that should not be counted, the average function has no problems calculating based on averages from the 1st through 31st. By that, I mean that shorter months will average only the 28-30 days with values instead of taking an average of 31 days.
=AVERAGE(D2:AH2)
Because this is set up as a table, I can easily filter by month and then sort by score. That's part of how I handle my monthly weather index updates.
My updated spreadsheet can be found at: https://onedrive.live.com/redir?resid=2E6EFB80915ED5BD!8567&authkey=!AA0ELNAux024KI0&ithint=file%2cxlsx
Notes
As I already stated, I created my solution when I created the Washingtonian Weather Index. Hopefully, I remembered all of the important details and make at least some sense.This month comparison tool could be revisited for future challenges.
No comments:
Post a Comment