intersections of rows and colums in calculations

[Chas]

Hello world, hope you can help on this topic:
I am using the formula that returns the value of an intersection
between a column and a row, as in "=(sales January)" where 'sales' is
the name of the row and 'January' is the name of the column. The name
'sales' was defined by selecting the total row and then use Insert
Name. Same principle for the column. So the value that is returned is
what is found on the intersection of the row 'sales' and colum
'January'.
So far so good. Now I want to calculate sales for the most recent three
month. So the formula would e.g. be =(sales March) + (sales February) +
(sales January). This works. But how do I progress to the next month
without having to change the formula? In other words, next month the
formula would be =(sales April) + (sales March) + (sales February), but
that would mean that I have to change the formula every month (it's a
big spreadsheet and it's undoable to change all the formulas every
month).
You would think that I could solve this by using not the name of the
month, but another name like 'most recent month', 'previous month' for
the next one, etc. But then I would have to move over the data to the
next colum every time (or rename the colums), which isn't doable either
due to the size/complexity of the whole thing.
So what I was hoping for is to find a formula like =(sales [column B])
where [column B] is a non-relative reference (i.e. it always remains
column B even if you insert another column). Any suggestions would make
my life significantly more pleasant.
Thanks, Chas

 

[Harlan Grove]

Chas wrote...
....

. . . The name
'sales' was defined by selecting the total row and then use Insert
Name. Same principle for the column. So the value that is returned is
what is found on the intersection of the row 'sales' and colum
'January'.

Insert > Name > Define or Insert > Name > Label?

So far so good. Now I want to calculate sales for the most recent three
month. So the formula would e.g. be =(sales March) + (sales February) +
(sales January). This works. But how do I progress to the next month
without having to change the formula? In other words, next month the
formula would be =(sales April) + (sales March) + (sales February), but
that would mean that I have to change the formula every month (it's a
big spreadsheet and it's undoable to change all the formulas every
month).

....

If you have a table in A1:E4 that looks like

________January__February__March__April
sales___ 100______ 105_____ 115____ 118
costs___ 75______ 85______ 90____ 95
margin__ 25______ 20______ 25____ 23

and all the rows and columns were defined as names (Insert > Name >
Define), then you could use formulas like

=SUM(INDEX(sales,MATCH("January",B1:E1,0))
:INDEX(sales,MATCH("March",B1:E1,0)))

or

=SUM(INDEX(sales,MATCH("January",B1:E1,0))
:INDEX(sales,MATCH("April",B1:E1,0)))

Range intersection was never meant to provide flexibility, which is
what you want. It was meant to work with defined labels to make cell
referencing 'easy', but in this case, 'easy' meant the opposite of
flexible and comprehensive. If you want to use range intersection, you
get to put up with its limitations.

Another alternative, if everything is defined as names.

=SUM(sales INDIRECT("January"):INDIRECT("April"))

Defined labels don't support this, but they could be used hardcoded as

=SUM(sales January:April)

 

[Herbert Seidenberg]

Since you want to sum the last 3 months,
but not change the formula when you insert another month,
try this formula:
=SUM(INDEX(sales,COLUMNS(sales)-3):INDEX(sales,COLUMNS(sales)-1))

Your data might look like this:

.. Jan Feb Mar Apr AA AC
sales 14 12 11 17 AB AD

The name <sales> includes one extra cell (14,12,11,17,AB).
When you insert a column immediately after Apr, the name
<sales> will expand automatically and the sum will now
include the sales number you enter under May.
In case you do not insert a column, but change AA, AC
to May, June, then you have to redefine <sales>.