Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reusing grouping of non-adjacent cells
I'll explain what I'm conceptually trying to accomplish, then how I
have tried and failed. I would like to be able to use ctrl+clicking to select non-adjacent cells in a column, then reuse that set in other formulas for doing things like averages or sums of columns. For example, I ctrl click in column B to select cells in rows 1,2,3, and 5. I want to maybe do a sum in column C for those rows, and maybe average in column D for those rows. I tried giving them a name, then doing soemthing like Sum(Offset(NamedSet,0,1)) But this gives #Value error as soon as Offset is evaluated. I just want to be able to redefine the set of rows that the other formulas should operate on, and have that cascade to the other formulas. Most of them are sum formulas, so even a solution with sum formulas would be nice. I was thinking of adding a column of checkboxes and somehow use the sumif formula, but the checkboxes are kinda funny in the way they "float" around and don't seem to actually reside inside a cell. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reusing grouping of non-adjacent cells
The problem is OFFSET().
OFFSET() does not work for disjoint ranges. Some functions like AVERAGE() and SUM() will work for disjoint ranges. Some functions like OFFSET() and COUNTIF() will not. You get to decide if this is a bug or an un-advertised deficiency. -- Gary's Student " wrote: I'll explain what I'm conceptually trying to accomplish, then how I have tried and failed. I would like to be able to use ctrl+clicking to select non-adjacent cells in a column, then reuse that set in other formulas for doing things like averages or sums of columns. For example, I ctrl click in column B to select cells in rows 1,2,3, and 5. I want to maybe do a sum in column C for those rows, and maybe average in column D for those rows. I tried giving them a name, then doing soemthing like Sum(Offset(NamedSet,0,1)) But this gives #Value error as soon as Offset is evaluated. I just want to be able to redefine the set of rows that the other formulas should operate on, and have that cascade to the other formulas. Most of them are sum formulas, so even a solution with sum formulas would be nice. I was thinking of adding a column of checkboxes and somehow use the sumif formula, but the checkboxes are kinda funny in the way they "float" around and don't seem to actually reside inside a cell. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reusing grouping of non-adjacent cells
In a standard Module enter:
Sub Foo() Range("C1").Value = WorksheetFunction.Sum(Selection) Range("D1").Value = WorksheetFunction.Average(Selection) End Sub Then Click on several of your numbers (while holding Down the Control key, as you say) that are in Column B Then Run Foo Hope this helps, Jim May " wrote in message ups.com: I'll explain what I'm conceptually trying to accomplish, then how I have tried and failed. I would like to be able to use ctrl+clicking to select non-adjacent cells in a column, then reuse that set in other formulas for doing things like averages or sums of columns. For example, I ctrl click in column B to select cells in rows 1,2,3, and 5. I want to maybe do a sum in column C for those rows, and maybe average in column D for those rows. I tried giving them a name, then doing soemthing like Sum(Offset(NamedSet,0,1)) But this gives #Value error as soon as Offset is evaluated. I just want to be able to redefine the set of rows that the other formulas should operate on, and have that cascade to the other formulas. Most of them are sum formulas, so even a solution with sum formulas would be nice. I was thinking of adding a column of checkboxes and somehow use the sumif formula, but the checkboxes are kinda funny in the way they "float" around and don't seem to actually reside inside a cell. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
How to concatenate adjacent cells in a range without using &? | Excel Worksheet Functions | |||
grouping of cells in protected sheets | Excel Worksheet Functions | |||
Can I use the grouping and lock and protect cells | Excel Discussion (Misc queries) | |||
I want to format a cell based on an adjacent cells value | Excel Discussion (Misc queries) |