Building Formulae in Excel

After having helped a work colleague to load the current tax formula into a single cell in an Excel spreadsheet that is to be used for budgetting wages, the next task they asked for help with was to display the number of pay weeks in each month. Now this was something that I managed to set up quite quickly because I knew enough about what was needed to be able to just start coding without any pre-planning but how would you go about tackling a complex problem in Excel where you don't know enough about how to solve the problem to just code it?

What you would need to do is to start by analysing the problem and work out how to solve the problem in general terms first and then work out what formulae to insert into Excel so as to get it to do each part of the necessary calculations for you. Once you get everything working you can then hide any cells that you have used for intermediate calculations and protect all the cells in the worksheet except those that need to be enterable to use the spreadsheet.

We'll use the problem that I was asked to solve as an example of the sorts of steps that you need to break the task into in order to be able to code a complete solution. The first thing that I said when presented with the problem was that there was no way that I would attempt to come up with a formula that could be placed just in the cells displaying the number of weeks in each month. I knew from the start that the calculation would be too complex for me to want to try to write it in a single cell. Anyway if you really need ed to do that you could rework the code after you get your first version working - although I can't see any reason why you would want to.

The first thing that you need when setting up a complex formula in Excel is to define the input fields that you need to have supplied in order to be able to perform the calculation. There are two things that you need to be able to work out how many pay weeks that there are in each month of the year. You need to know when the year starts and which day of the week is payday. I knew this as soon as the problem was presented to me but I then had to explain to the person who had asked me to solve this problem just why these two things were needed. The first of these is needed because the months don't start on the same day of the week every year and so you need to know when the month starts to know which days of the week occur four times in the month and which occur five. You then need to know which day of the week is payday in order to work out whether that day of the month occurs four or five times. By analysing your problem and working out what inputs you will need and why you need them you can plan out just what you need to work out in order to be able to work out the final answer.

Now in Australia the financial year runs from July of one year through to June of the next. So the date that we need will be 1st July that is the start of the financial year we want to calculate the weeks for. The Excel function that returns the day of the week returns 1 for Sunday and 7 for Saturday so we need to identify which day of the week is payday using a corresponding number between 1 and 7. When setting up the input fields to solve your problem you need to similarly identify what exactly will need to be entered that will supply all of the information that you will need to be able to calculate the required answer (in this instance twelve values each either 4 or 5 representing the number of times that a weekly payday occurs within each month of the year).

The next step once you have an overall idea of the what your code needs to do and the input fields defined is to break the calculation down into manageable pieces so that you can then code them one at a time. For the pay days in each month problem the first step is to determine the date of the first payday within the year. If you can't see how this will help us to solve the problem then visualise a calendar for the month where we mark the first payday of the month. The remaining paydays for the month will be the three or four days immediately below it on that calendar. The formula to work out what date on or after the date that we entered that falls on the day of the week that we entered is simple enough to put in a single cell. Basically we extract the day of the week from the entered date using the supplied Excel function that returns that, if we subtract that from 8 and add the day of the payday (and then subtract 7 if the result is greater than 7) then we have the first day on or after the start date that is within that month.

We now need to count how many days there are in that column of the calendar. We can do this by getting the date 35 days after this one. If that date is in the same month then our first month has 5 weeks and if it is in the next month then the month has 4 weeks. This formula is simple enough to go in the cell that is to display the 4 or 5 result for our first month.

To work out the number of weeks in subsequent months we need the number of weeks in all the prior months added together and then add 5 to that and multiply by 7. Add that many days to the first pay day of the year and we will either have the last payday of a month that has 5 weeks or the first payday of the following month if the month only has 4 weeks. This formula can easily be replicated for all of the remaining months of the year so as to complete the solution to the entire problem.

As you can see, most of the work in solving a problem like this is in working out the initial values that can be used as the starting point. In this instance once we had the number of weeks in the first month and had worked out what change we needed to get the number of weeks in the second month, it was trivial to extend this to cover all the remaining months (if you wanted to project forward over multiple years you'd just extend the same formula down the columns to cover as many months into the future from the start date as you need.

Most likely the problem that you will need to solve in your Excel spreadsheet will be completely different from this one but by examining the above information on selecting the input fields and working out the formulae that you need to convert those inputs to the desired result, you will have some idea of the approach to use to tackle whatever particular problem that you have to solve. In most cases you should break the problem down into a number of small much easier to solve steps and code and test each piece before moving on th=o the next (after you have planned it out to determine what steps are appropriate to solve the problem).

The person who presented me with the above problem was more knowledgeable with Excel that I was and so was able to quickly type in the formulae as I told them what was needed. They most likely could have worked through the problem to break it up into the necessary steps to get the solution for themselves. The only reason they asked me was that they knew that I could tell them what calculations were needed without having to work it all out first because I had written enough date manipulations in other languages that I knew what fields would be needed and could work out what each step needed to be as they entered the formula for the prior step.

go to top

FaceBook Follow
Twitter Follow