Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal of Abs of a range?
Greetings! Am stumped. Need a sum of the absolute value of row-filtered
elements in a column. SUBTOTAL does this admirably, but it appears that SUBTOTAL does not accommodate anything but a simple range as it target, in the pseudo-syntax SUBTOTAL(Function,Range). SUM, on the other hand, allows for a function such as ABS to be embedded as its argument, but does not screen out filtered values. A work-around is to create an intermediate column that ABS's the original column and then apply SUBTOTAL to it. However, in the interest of compactness, the question becomes: is there a way to cause SUM to skip filtered-out elements, or a way to create the equivalent of SUBTOTAL(9,ABS(Range)). Thanks! George |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal of Abs of a range?
Incidentally, SUM as imagined here would be expressed as {SUM(ABS(Range))},
an array formula, that in this form doesn't skip filtered-out rows. It appears that SUBTOTAL does not support array usage (or is it vice versa?). "G Lykos" wrote in message ... Greetings! Am stumped. Need a sum of the absolute value of row-filtered elements in a column. SUBTOTAL does this admirably, but it appears that SUBTOTAL does not accommodate anything but a simple range as it target, in the pseudo-syntax SUBTOTAL(Function,Range). SUM, on the other hand, allows for a function such as ABS to be embedded as its argument, but does not screen out filtered values. A work-around is to create an intermediate column that ABS's the original column and then apply SUBTOTAL to it. However, in the interest of compactness, the question becomes: is there a way to cause SUM to skip filtered-out elements, or a way to create the equivalent of SUBTOTAL(9,ABS(Range)). Thanks! George |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal of Abs of a range?
{=SUM(ABS(Range))}, to be more exact ...
"G Lykos" wrote in message ... Incidentally, SUM as imagined here would be expressed as {SUM(ABS(Range))}, an array formula, that in this form doesn't skip filtered-out rows. It appears that SUBTOTAL does not support array usage (or is it vice versa?). "G Lykos" wrote in message ... Greetings! Am stumped. Need a sum of the absolute value of row-filtered elements in a column. SUBTOTAL does this admirably, but it appears that SUBTOTAL does not accommodate anything but a simple range as it target, in the pseudo-syntax SUBTOTAL(Function,Range). SUM, on the other hand, allows for a function such as ABS to be embedded as its argument, but does not screen out filtered values. A work-around is to create an intermediate column that ABS's the original column and then apply SUBTOTAL to it. However, in the interest of compactness, the question becomes: is there a way to cause SUM to skip filtered-out elements, or a way to create the equivalent of SUBTOTAL(9,ABS(Range)). Thanks! George |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal of Abs of a range?
Try this:
The range to sum is B2:B10 (filtered or unfiltered): Array entered: =SUM(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),,1)),ABS(B2:B10))) Biff "G Lykos" wrote in message ... Greetings! Am stumped. Need a sum of the absolute value of row-filtered elements in a column. SUBTOTAL does this admirably, but it appears that SUBTOTAL does not accommodate anything but a simple range as it target, in the pseudo-syntax SUBTOTAL(Function,Range). SUM, on the other hand, allows for a function such as ABS to be embedded as its argument, but does not screen out filtered values. A work-around is to create an intermediate column that ABS's the original column and then apply SUBTOTAL to it. However, in the interest of compactness, the question becomes: is there a way to cause SUM to skip filtered-out elements, or a way to create the equivalent of SUBTOTAL(9,ABS(Range)). Thanks! George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i insert a subtotal from a range of dates | Excel Worksheet Functions | |||
HOW CAN I INSERT A SUBTOTAL FROM A RANGE OF DATES | Excel Worksheet Functions | |||
Subtotal Range of Alphanumeric References | Excel Worksheet Functions | |||
subtotal a range of cells on a different worksheet | Excel Worksheet Functions | |||
SUBTOTAL(9,range) Does NOT work in AutoFilter | Excel Worksheet Functions |