Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
soteman2005
 
Posts: n/a
Default Help with using range names in sum function


Hi,

I am creating a model in excel using range names for every row which
works really well. The model is run across several years which are
allocated a column each. I need to sum some cells which are all in 1
year e.g. =sum(b1:b5) but when i use the range names for row 1:row 5,
excel sums the entire named range from A1:IX1 to A5:IX5, instead of
just the cells in the active column.

Does anyone know a way to do this? I'm thinking that there might be an
intersect function or something which will allow me to sum the
intersecting cells of the named column range and named row ranges?

Similarly, I would also like to be able to reference a cell from the
previous year to do an average, so is there a way to do that using the
range name i.e I am calculating a 2006 average but I need the 2005
value for susbcribers, but the name range gives me the 2006 value.

I am working across years 2003 to 2013. There are quite a few instances
in which I want to sum data, but here is one example;

2003 2004 2005 etc.
Cost A £12 £13 £15
Cost B £10 £11 £5
etc.
Total £22 £24 £20

but obviously I have more rows to sum and more years. I have range
named each row as I have about 450 rows in each worksheet and I often
need to add extra rows.

so for total I would use a formula like =sum(costA:costB) using range
names, but that sums CostA for 2003, 2004, 2005 and Cost B for 2003,
2004, 2005 which I obviously don't want.

The issue with referencing previous years might for example be doing an
average cost for 2003-2004 so I would want to be able to reference CostA
2003 whilst in the 2004 column and I have no idea how to do this with
range names.

Any help would be greatly appreciated.

Many thanks


--
soteman2005
------------------------------------------------------------------------
soteman2005's Profile: http://www.excelforum.com/member.php...o&userid=29078
View this thread: http://www.excelforum.com/showthread...hreadid=488743

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default Help with using range names in sum function

You should look up the OFFSET function in Help. This enables you to
define a dynamic (i.e. varying) range relative to a base range, using
variable values. It is reasonably easy to use OFFSET to refer to a
subset of a given named range. If you would like to see how it is
done, send me your spreadsheet, via Google groups, or upload it in
excelforum and I will show you.

Example: =SUM(OFFSET(YR2005,0,2,1,3)) will calculate the sum of cells
3 through 5 of the first row of a range called YR2005 (referring to the
first cell as "cell 1"). We are defining a new range "offset" from the
top left hand corner of YR2005 (the first parameter) by 0 rows (the
second parameter) and 2 columns (the third parameter) with a depth of 1
row and a width of 3 rows (third and fourth parameters).

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Help with using range names in sum function

The intersect operator is the space character. So "A:A 1:1" means "A1".
You may have to experiment with it a lttle before you get it to do what you
require.

--
Kind regards,

Niek Otten

"soteman2005"
wrote in message
...

Hi,

I am creating a model in excel using range names for every row which
works really well. The model is run across several years which are
allocated a column each. I need to sum some cells which are all in 1
year e.g. =sum(b1:b5) but when i use the range names for row 1:row 5,
excel sums the entire named range from A1:IX1 to A5:IX5, instead of
just the cells in the active column.

Does anyone know a way to do this? I'm thinking that there might be an
intersect function or something which will allow me to sum the
intersecting cells of the named column range and named row ranges?

Similarly, I would also like to be able to reference a cell from the
previous year to do an average, so is there a way to do that using the
range name i.e I am calculating a 2006 average but I need the 2005
value for susbcribers, but the name range gives me the 2006 value.

I am working across years 2003 to 2013. There are quite a few instances
in which I want to sum data, but here is one example;

2003 2004 2005 etc.
Cost A £12 £13 £15
Cost B £10 £11 £5
etc.
Total £22 £24 £20

but obviously I have more rows to sum and more years. I have range
named each row as I have about 450 rows in each worksheet and I often
need to add extra rows.

so for total I would use a formula like =sum(costA:costB) using range
names, but that sums CostA for 2003, 2004, 2005 and Cost B for 2003,
2004, 2005 which I obviously don't want.

The issue with referencing previous years might for example be doing an
average cost for 2003-2004 so I would want to be able to reference CostA
2003 whilst in the 2004 column and I have no idea how to do this with
range names.

Any help would be greatly appreciated.

Many thanks


--
soteman2005
------------------------------------------------------------------------
soteman2005's Profile:
http://www.excelforum.com/member.php...o&userid=29078
View this thread: http://www.excelforum.com/showthread...hreadid=488743



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a range variable inside a excel function Michael Excel Discussion (Misc queries) 2 November 14th 05 03:52 PM
Replace range names with cell references? KH Excel Worksheet Functions 2 August 2nd 05 01:09 AM
Function to determine if any cell in a range is contained in a given cell [email protected] Excel Worksheet Functions 3 February 7th 05 05:19 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM
range names Pedro Excel Worksheet Functions 1 November 9th 04 07:27 PM


All times are GMT +1. The time now is 12:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"