I recently received a very interesting email from a retired member of the military asking some help with a golf scoring issue. Needless to say that on so many levels I was more than happy to help out. While this blog is about “Experiments in Finance, it’s fair to say that many finance lovers like to play golf as well. It’s not the easiest problem to explain but hopefully with his comment and a preview of his spreadsheet you will get the idea. Here we go:

“I need help with writing an “IF” formula in excell. Background: I run a local golf league with 40 to 70 golfers playing each week. Instead of using strokes and keeping up with the handicaps, I use a point system. Each week I have to manually calculate each man’s score, plus or minus, from his required points.

Example: For myself I currently am required to make 45 points. If I make within plus or minus 2 of the 45 points there is no change to my next weeks requirement. However, if I make minus 3 or more points, my score will drop 1 point(to 44). If on the other hand, I score 3 or 4 points above 45, my new point requirement increases by 1 point, if I score 5 or 6 points above, my requirement increases by 2 points, if I score 7 or more points, my requirement increases to 3 points. I currently use an excel spreadsheet, listing the players . I enter their that days point total and then manually do the math and enter their new point requirement. I can continue to do the math…but in this day and age I would like to work “smarter not harder”"

Here is an example of his spreadsheet:

First off, the points that he discussed are not exactly the same as the ones in the spreadsheet but that is not a problem, we are looking for a flexible solution anyway.

I used the following method:

-There are 4 possible answers:
-”a”
-a given range (in this case, between -1 and 5)
-lower than that range
-higher than that range

Basically, I will first use “if conditions” to check if any of the first 3 occur, and then refer to a range of results if it’s not the case.
The following formula looks more complicated than it really is:

=IF(C3=”",”",IF(C3=$V$4,B3+$W$4,IF(C3-B3>$V$5,B3+$W$5,IF(C3-B3<$V$6,B3+$W$6,B3+VLOOKUP(C3-B3,$V:$W,2,FALSE)))))

basically, it checks if the previous score made is filled, if it the result is either "a", higher than the range, lower than the range and applies the adjustment depending on those.

If none of those conditions are met, it simply checks the range.

You can modify the range or values at any time by changing columns "U" and "V"

You can also simply copy the blue cells to a new column "req". try copying from P3:P22 to R3:R22.

Here is what the new spreadsheet looks like:

I also invite you to download the spreadsheet to see for yourself! As is always the case, please feel free to send me your own excel related issues, I’d love to help

Popularity: 1% [?]

The other day, I got a question about using dates in excel spreadsheets. It’s very tricky to use both dates and numbers in Excel and certainly not as easy as it should be. Before getting started, I would love to invite you to ask us any questions that you might have. You can simply go to our contact page.

Back to our spreadsheet, many users email me every month with questions regarding dates in excel. The difficult part about writing posts is that almost every problem is a bit different compared to vlookup or sumif issues which are almost all similar. Today’s issue is a rather common one. First let’s take a look at the spreadsheet:

The objective is to fill out the last 2 columns. First off, we determine if the task is overdue. It’s quite simple to find out if it is, as we have today’s date. If the due date is further than today’s date, the number should be over 0. However, we cannot have “negative days”. Therefore, I will be using this function:

=MAX($D$3-E6,0)

Then, the status should be either “Outstanding” or “Closed”. In this case, Outstanding means:

-There has not been a “response date”
-Overdue days are over 0

I will use the “Nested Excel And function” that I presented some time ago. Here is the formula that I will use:

=IF(AND(G6>0,F6=”"),”Outstanding”,”Closed”)

I also added conditional formatting to make it look even more obvious when issues were “Outstanding”. This is what it looks like:

I also invite you to download the spreadsheet for additional information here:)

Popularity: 1% [?]

A few weeks ago, we had presented a good way to build a mortgage calculator in excel. We received a lot of positive feedback and suggestions which we always appreciate. One of those was that we could have used a dropdown menu instead of having users type the payment frequency. I agree 100%. Why?

-Dropdown menu would diminish the time required to fill out the form
-More Importantly: A dropdown menu would help us avoid typos by those filling out the form.

There is no good argument to not use a dropdown menu except for not knowing how to do it. I decided to write a quick post to go over how to add a dropdown menu in excel. It’s a lot easier than you could imagine. If you remember, this was the form that was currently in the spreadsheet:

What I would like to do is simply avoid having to type “bi-weekly”. How? There are a few different ways to do it. The first question you should ask yourself is:

-Do I want to define the possible answers?

The answer is yes in this case (it usually is) as I want to only 4 give 4 options for the menu. How do I proceed? First off, anywhere in the spreadsheet, I should write down the 4 possible choices, in the cells next to each other. Here is what I will do:

You could put this on another sheet, hide those rows or change the color, it’s not important for the user to see them. Then, I will create the dropdown menu. In excel 2003, I will select the cell where I want it and select “data/validation”:

Then, in the “settings” tab, simply select “source” and choose the possibilities. In my case, I select the 4 cells:

Then I click ok and you can see the result:

That’s it? Looks easy? It actually is. You can download our new improved spreadsheet with the dropdown menu here

Popularity: 1% [?]

Dear Readers,

We have been fortunate to get many different emails about your excel questions and issues and would love to get even more feedback from you.

What type of information are you looking to get?

Which functions do you have trouble using?

Do you also use macros and if so, do you need any assistance doing so?

What would you like to do through excel but have trouble doing?

Are there tasks that you perform very often that should/could be automated but that you do not know how to do?

Please either write a comment or email at thefinancialblogger (at) gmail (dot) com.

You can also give us the questions here!

Popularity: 1% [?]

You would think that finding the interest rate being paid on a credit card or another type of loan would be easy right? I mean, the statements generally do include the interest rate that you are being charged. Of course, there are many different ways to present an interest rate and while the ideal and most representative way would be to always include the effective rate, it is not always in the advantage of the lender to present it that way. Why? Because that rate is generally higher and could encourage you to look for a better deal. The companies figure that consumers will not see the difference and in almost all cases they are correct.

Here is a simple way to calculate your charged interest rate, in this example we will use a simple loan but this could be adapted to a number of different situations. You will need a number of different information in order to make the calculation.

End of last period: August 25th
End of this period: September 23rd
Outstanding Loan at the last period: $18,575.00
Interest Rate Charged: $111

Let’s review the formula to calculate interest:

Interest = Loan Amount * Interest Rate * (nb days/365)

In this case, we are missing the interest rate so we will shuffle around the formula:

Interest Rate = Interest / (Loan Amount/ (nb days/365)

In our example that would be:

Interest Rate = $111/18575/(29/365) = 7,52%

See a chart here or you can even download the spreadsheet here

Popularity: 1% [?]