Monday Morning Mulling: March 2024 Challenge
1 April 2024
On the final Friday of each month, set an Excel for you to puzzle over for the weekend. On the Monday, we publish one suggested solution. No-one is stating this is the best approach, it’s just the one we selected. If you don’t like it, lump it – or contact us with your preferred solution.
The Challenge
On Friday, we raised a challenge that some of you may have already come across. Consider the following example:
You can download the original question file here. As you will see, in cell F12 (pictured) we have the formula
=5*7
Similarly, the two formulae in cells F13 and F14 were not calculating either. All you had to do was force a calculation as simply as possible without recreating the formula elsewhere.
Suggested Solution
The second formula, in cell F13, perhaps gives the game away:
=sqrt(pi())
This expression should be taking the square root (SQRT()) of the number pi (π) (PI()). However, functions in Excel capitalise automatically. This expression hasn’t. It is not being recognised as a formula.
The formulae
=5*7
=sqrt(pi())
=CHOOSE(2,F12,F13)
will not calculate. They merely display as above. But hang on a minute: why don’t these calculations compute when I write them here? Well, aside from the fact I am not in Excel, these are text expressions. The ‘equals’ sign (=) is meaningless here. And that’s the problem in Excel. Take a closer look at the Home tab for these cells:
Do you see the cells F12:F14 are formatted as Text? If you were to multiply each cell by one [1], you would get #VALUE! Text doesn’t calculate. It’s as simple as that.
Well, not quite. First, let’s change the formatting to something other than Text, say, General:
This does not have an immediate effect:
However, if you now edit each cell (e.g.press F2) and then hit ENTER, the formulae will calculate as intended:
Problem solved. The solution file can be inspected here.
Word to the Wise
Not all of our problems need PhDs in Astrophysics and the patience of Dr Garcia. This one was incredibly simple if you knew what to do. You might think this month’s challenge was a “cop out” but we decided to include this one as it is amazing how often this problem occurs in practice and stumps fairly experienced modellers. Until next month.
The Final Friday Fix will return on Friday 26 April 2024 with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.
