Hi Joe ,
A complicated formula is not necessarily more difficult to develop than a simple one ; all formulae are built up , using Excel functions ; it is in how these functions have been put together to arrive at the formula , which in turn solves a problem , that the magic is.
The first step is therefore to know how the bricks can be put together ; the more intricate the brick , the more difficult it is to combine it with other intricate bricks.
Some intricate bricks are : MATCH , COUNTIF , LOOKUP ( this is totally different from HLOOKUP and VLOOKUP , so don't get confused ) , FREQUENCY , MMULT , probably in order of difficulty.
One trick in most of the above is when a range is used where a scalar value is used e.g. most people are familiar with what MATCH does if you use it in a construct such as :
=MATCH(A1,B1:B5,0)
How many are familiar with what happens if the construct is changed to :
=MATCH(A1:A5,B1:B5,0)
This is something which even the Excel help might not explain with examples. Try it out in various situations to see exactly what it does. Your help in all this is Excel's in-built Formula Evaluator.
The same goes for COUNTIF , where the second parameter is normally a scalar ; try using a range for this , and see what happens. It goes without saying that in all such cases , the formula needs to be array-entered , using CTRL SHIFT ENTER.
LOOKUP and FREQUENCY are something else altogether ! There are so many variations possible , I sometimes feel they can replace all other Excel functions !
LOOKUP without a third parameter behaves differently from LOOKUP with a third parameter ; FREQUENCY using a positive first parameter behaves differently from FREQUENCY using a negative first parameter.
Try to understand how the formula given by Sajan does the job in the following thread :
http://chandoo.org/forum/threads/how-to-get-the-values-with-the-largest-sum.16540
Narayan
