Friday, October 2, 2009

A dynamic formula in Excel

Have you ever wondered if you can total a filtered list as and when you change the filter criteria...!
Yes indeed... try the "subtotal" function. The "subtotal" function offers a unique system where in you can calculate average, sum, count, max, min, standard deviation and variance. All these calculations can be performed by just changing the syntax of the "subtotal" function.

The generic syntax of the "subtotal" function is =subtotal(function_number, cell reference).

Here "function_num" refers to the operation you wish to perfom. The list of numbers are:
Average -     1
Count -        2
CountA -      3
Max -          4
Min -           5
Product -     6
STDev -       7
STDevP -     8 (standard deviation for a population)
Sum -          9
Variance -   10
VarianceP - 11

for example: if you wish to sum a given range (say B1:B25) then your formula will be =subtotal(9,B1:B25)

To apply this on a list where you wish to filter and check results of the filtered list - you must first use this formula for the complete list. Then once you filter the data, the result changes automatically.

Try it out and let me know if this helps...! Even if you need any further help on this, do let me know...!!

No comments:

Post a Comment