Friday, 5 December 2008

Two New Excel Functions

Microsoft has just announced two new Excel functions to be included in the Microsoft Office 2007 suite, Service Pack 2 (SP2) expected to be released 04/01/2009. I'm sure they will prove to be indispensable tools to accountants, particularly those involved in budgeting, financial reporting or financial modeling for charities and public corporations.

In these trying economic times, closing the gap between revenues and expenses can be challenging to say the least. Also, with consumer confidence ebbing and the price of overseas parts and raw materials mounting, it is increasingly difficult for companies to maintain their profit margins. That's where the first function, HOPE comes in. It will calculate the gap or margin that you will hopefully fill when your forecasted transactions are realized. Here's an excerpt from the documentation:



HOPE returns a specific dollar amount or percentage, based on the total revenue and total expense/cost cells you specify.



Result is either "%" for a margin calculation or "$" for a dollar amount.

Tot_revenue is the total revenue amount or the location of the cell containing the revenue amount.

Tot_cost is the total expense or cost amount or the location of the cell containing the cost amount.


  • If tot_revenue is greater than tot_cost, HOPE is unnecessary.
  • If the margin or dollar amount calculation is too high, HOPE returns the #VALUE! error value.

I should comment that in testing the beta version of this function I came across the #VALUE! error and discussed it with one of the developers. He said that the function is based on econometrics. There is a certain point where the gap between revenues and expenses/costs is so great that the value of HOPE required to balance the equation approaches infinity (i.e. an undefined value). In order to prevent the function from going into an infinite loop, it aborts and displays the error message.

To overcome this issue, Microsoft has introduced a second function, but you should employ it cautiously as it uses an enormous amount of system resources. The syntax is similar to HOPE, but the function is called PRAY.


Archie said...

Thanks for pointing us to these new functions, Bill. I downloaded the beta version too, and noticed a #DIV/0! result when tot_revenue was 0, but ISERR() returns FALSE in this case, which I thought was a bug. Microsoft Excel Help points out that although this appears to be an error message, in the context of the HOPE() function the #DIV/0! result actually is a mnemonic which is may be interpreted as follows:
# Attention
DIV Divine Assistance Recommended
/ Bow Your Head
0 Open Your Mouth
! Cry Out for Help

Bill Kennedy, CA said...

Hi Archie,

Thank you for taking the time to test building HOPE into your business model. I wish you every success.