Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ratio of growth over a filtered set
Assume the following sheet, with columns titles Color, Yesterday, Today
Blue 5 7 Blue 0 9 Red 3 9 It is filtered using an autofilter on Color, and I need to show the total for Today, but I need to exclude rows with a 0 value in the Yesterday column. For instance, when filtered on Blue, I need to get 7 I realize I can use SUBTOTAL to get the sum of the C column across the filtered rows, how do I exclude rows with a 0 value in column B from the SUBTOTAL ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ratio of growth over a filtered set
Assuming that A2:C4 contains the data, try...
=SUMPRODUCT(--($B$2:$B4<0),SUBTOTAL(9,OFFSET($C$2:$C$4,ROW($C$2 :$C$4)-RO W($C$2),0,1))) Hope this helps! In article , AlanKohl wrote: Assume the following sheet, with columns titles Color, Yesterday, Today Blue 5 7 Blue 0 9 Red 3 9 It is filtered using an autofilter on Color, and I need to show the total for Today, but I need to exclude rows with a 0 value in the Yesterday column. For instance, when filtered on Blue, I need to get 7 I realize I can use SUBTOTAL to get the sum of the C column across the filtered rows, how do I exclude rows with a 0 value in column B from the SUBTOTAL ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ratio of growth over a filtered set
Yes, it works. I wish I would understand it though. Where can I find a
description of what "--" does ? Many thanks. "Domenic" wrote: Assuming that A2:C4 contains the data, try... =SUMPRODUCT(--($B$2:$B4<0),SUBTOTAL(9,OFFSET($C$2:$C$4,ROW($C$2 :$C$4)-RO W($C$2),0,1))) Hope this helps! In article , AlanKohl wrote: Assume the following sheet, with columns titles Color, Yesterday, Today Blue 5 7 Blue 0 9 Red 3 9 It is filtered using an autofilter on Color, and I need to show the total for Today, but I need to exclude rows with a 0 value in the Yesterday column. For instance, when filtered on Blue, I need to get 7 I realize I can use SUBTOTAL to get the sum of the C column across the filtered rows, how do I exclude rows with a 0 value in column B from the SUBTOTAL ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ratio of growth over a filtered set
In article ,
AlanKohl wrote: Yes, it works. I wish I would understand it though. Have a look here... http://j-walk.com/ss/excel/eee/eee001.txt Where can I find a description of what "--" does ? Have a look here... http://www.mcgimpsey.com/excel/formulae/doubleneg.html Many thanks. You're very welcome! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Divide x/y should give Ratio x:y,How to express ratio in excel | Excel Worksheet Functions | |||
Ratio Formula | Excel Discussion (Misc queries) | |||
How do I express a ratio? | Excel Discussion (Misc queries) | |||
Ratio function | Excel Worksheet Functions | |||
ratio formatting | Excel Discussion (Misc queries) |