Excluding "0" in standard deviations
Hi,
In calculation standard deviations I am using the formular =SUBTOTAL(7;B2:B100) which appears to work nicely when filtering. However, it also includes cells with "0". Can anyone tell me, how the formular needs to be changed so only numbers "0" is included? Many thanks in advance, Niclas |
Excluding "0" in standard deviations
Subtotal works only on filtered set... so you should get ther right answer if
you filter out the cells with 0. It does include cells which are hidden... to exclude hidden cells use 107 instead of 7. "Niclas" wrote: Hi, In calculation standard deviations I am using the formular =SUBTOTAL(7;B2:B100) which appears to work nicely when filtering. However, it also includes cells with "0". Can anyone tell me, how the formular needs to be changed so only numbers "0" is included? Many thanks in advance, Niclas |
Excluding "0" in standard deviations
Thanks, filtering is of course a possibility. I hadn't thought of that.
But is there also a clever way to have this done automatically? I have a great number of columns where I need to calculate std.dev., so this would be much preferable, not least as I will also be filtering on other criteria. Niclas "Sheeloo" skrev: Subtotal works only on filtered set... so you should get ther right answer if you filter out the cells with 0. It does include cells which are hidden... to exclude hidden cells use 107 instead of 7. "Niclas" wrote: Hi, In calculation standard deviations I am using the formular =SUBTOTAL(7;B2:B100) which appears to work nicely when filtering. However, it also includes cells with "0". Can anyone tell me, how the formular needs to be changed so only numbers "0" is included? Many thanks in advance, Niclas |
Excluding "0" in standard deviations
Do you really need to use SUBTOTAL?
How about =STDEV(IF(A1:A80,A1:A8)) which is an array formula and must be entered with CTRL+SHIFT+ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Niclas" wrote in message ... Hi, In calculation standard deviations I am using the formular =SUBTOTAL(7;B2:B100) which appears to work nicely when filtering. However, it also includes cells with "0". Can anyone tell me, how the formular needs to be changed so only numbers "0" is included? Many thanks in advance, Niclas |
Excluding "0" in standard deviations
Thanks, I've already tried this formula, but it returns a value for the full
dataset (0), irrespective of any filtering. The subtotal on the other hand does respect the filtering, yet also includes the "0". So at this point, none of the two alternative fulfill my needs. Hope you can help with another option. Niclas "Bernard Liengme" skrev: Do you really need to use SUBTOTAL? How about =STDEV(IF(A1:A80,A1:A8)) which is an array formula and must be entered with CTRL+SHIFT+ENTER best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Niclas" wrote in message ... Hi, In calculation standard deviations I am using the formular =SUBTOTAL(7;B2:B100) which appears to work nicely when filtering. However, it also includes cells with "0". Can anyone tell me, how the formular needs to be changed so only numbers "0" is included? Many thanks in advance, Niclas |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com