Help Understanding Excel Formulas

[purceld2]

Could anybody point me in the right direction as to how I can understand Excel formulas. I am coming across formulas which I am finding difficult to understand. Are there any syntax rules etc. Tips to debug. Tools / Books which help write formula.

For example the formula below

=COUNTA(OFFSET(D18,0,MATCH(C$1,D$1:IV$1,FALSE)-1,1,12))

Thanks for any help in advance

 

[laughingdevil]

I've just learnt them as I go, usually a quick internet search helps.

With your specific example

=Counta - This counts the number of cells in a range that are not empty, it does not count or some their contents in anyway

Everything then winthin the outer brackes is defining the range to be counted.

OFFSET is used for dynamic ranges, in this case the Dynamic range starts at D18, looking across 0 rows, and then usually you'd have the number of columns, this though has been replaced by the match statement -1 so the result of this statement -1 is taken as the number of columns.

The Match function is looking for what's in C1, in the range D1:IV1, the FALSE is slightly unusal though as normally you'd have either 1, 0 or -1 here defining how Excel does the looking. The default search will look for the largest value that is less than or equal to whatever is in C1.

Hope that makes some sort of sense.