• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

OFFSET is not always to be avoided

N

NARAYANK991

Excel Ninja
Hi ,

Recently there was a problem posed by a member of the ExcelHero group on LinkedIn ; the problem and the full discussion can be accessed here :

http://www.linkedin.com/groups/True...-b2_anet_digest-null-2-null&ut=3lksTDjh-B-681

Following the formula proposal by Peter Bartholomew , there was an alternative proposal by Bernd Plumhof , and thereafter a comparison of four different methods of solving the same problem , which can be accessed here :

http://sulprobil.com/Get_it_done/IT.../Mortality_Annuities/mortality_annuities.html

Narayan
 
Nice comparison of different methods. I like the report by Fast Excel at the end. I'm sure the debate of OFFSET vs. INDEX will continue, but I think one point that is not covered in the speed comparison is volatility. While the OFFSET functions are faster calc (5.42 vs 6.27 microsecs), there might be changes made to sheet that don't require a recalculation, in which case the overall time for the INDEX functions would prevail.
 
Hi Luke ,

You are right ; my take away from the whole discussion is :

1. The problem was posed by Brian Canes , a finance professional , and dealt with actuarial calculations with which I am sure he is familiar. However , the problem was posed in a manner that it looked like all that he needed was help in developing an Excel formula.

2. The ExcelHero forum is full of Excel masters ; a formula-based solution , involving a somewhat higher than normal level of mathematics , was given , with the formula itself being several hundred characters long.

3. The problem was approached from a different angle by Bernd Plumhof , since he is also in Risk Management , and is presumably extremely well-versed in the theoretical aspects of what Brian Canes had posed. In that sense , he used Excel the way it should be used viz. a tool to solve problems , not necessarily one to develop 'monster' formulae.

4. In the process , more than one solution was developed , and it could be clearly seen that the 'monster' formula was the least efficient way to solve the problem !

5. The most efficient solution involved neither complicated formulae nor VBA but simple lookup tables which are even otherwise an integral part of the actuarial profession.

Which brings me to something which I have always stressed viz. Excel is finally a tool ; a violin is finally a musical instrument ; a performer may play intricate tunes on it , may play fast , may impress the listener with proficiency , but if the end result does not move the listener the way music should , then all the proficiency is irrelevant.

The end result for everyone is for a problem to be resolved ; as is normal , there may be several ways to achieve this ; it is not always necessary that a formula is the only way , or even the best way.

Mastery of Excel is the sum of a lot of knowledge , starting from the basics of how to structure your data and develop your worksheets to the use of templates , tables , CF , DV , pivot tables , charts ,... the list is long. It is more important for almost everyone to have this sound base and then to build on it by becoming a formula master ; instead , I find a lot of posters take away intricate formulae from forums , without ever having the basic grasp of Excel's power and all that it has to offer. In all this , I have not even touched on VBA , which is a whole world in itself ; but again , VBA is not everything ; one can be an Excel master without necessarily being a VBA genius.

Narayan
 
Hi Narayan Sir, thanks for such a nice article.
a violin is finally a musical instrument ; a performer may play intricate tunes on it , may play fast , may impress the listener with proficiency , but if the end result does not move the listener the way music should , then all the proficiency is irrelevant.
Click to expand...

This says all.
I remember there was a discussion about volatile function on the Forum. Not able to find the link.

Below is the link of @jeffreyweir 's article.
http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

P.S. - I do not intend to start a debate, just providing a link so that users can read both views.
 
@ThrottleWorks
There was some discussion here, although my reply in post #21 has been mangled by some erroneous quote/code tags - perhaps a moderator would fix it?

EDITED (SirJB7)
Just read it, followed the link, gone to #21 and everything seemed Ok, surely yet fixed. If it's something I'm as dumb as not seeing it please advise.
 
Last edited by a moderator:
@NARAYANK991

Sir, being said so well statements in comment #4, I would like to request you and other Excel Experts on this site if they can post some examples of good data layout (Structure) from real life problem what they have come across.

Regards,
 
You must log in or register to reply here.
Back
Top