help to build a formula

[rbriceno]

Hello dear members of the forum. I'm trying to design a formula that allow me to simplify some calculations. I have 35 columns of data.

In cell B90 I should execute this formula:
[TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl64, width: 80"]=sum(C44:C78)/sum(B44:B78)
In cell C90 the following:
[TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl66, width: 80"]=sum(D44:D77)/sum(C44:C77)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

As it can be observed the range is reduced by 1, each time I change column. What I did was to include a row with produces the number associated to the row on each formula: so I have the 78, 77 and so on.
I also included a formula in another row whose output is the letter of the column (B, C, etc.)

What I would like to have is another formula that allow me to bind those previous results, producing automatically the formulas I need for each column, so I don't have the edit the range of the sum if I copy the traditional sum formulas.

I can send an example if somebody need it in orden to understand better.

 

[James006]

Hello,

In cell B90 ... you could test

=SUM(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),1,"")&"44"):INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),1,"")&(80-COLUMN())))/SUM(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"44"):INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&(80-COLUMN())))

and then copy the formula

Hope this will help

 

[ukmikeb]

Hi

Try in B90 :-

[FONT=Verdana]=SUM(OFFSET(C$44,,,35-(COLUMNS($B90:B90)-1),1))/SUM(OFFSET(B$44,,,35-(COLUMNS($B90:B90)-1),1))[/FONT]

and drag across.

 

[Peter_SSs]

You could also avoid the volatile functions like OFFSET & INDIRECT with this formula in B90, copied across.

=SUM(C44:INDEX(C44:C78,36-COLUMNS($B:B)))/SUM(B44:INDEX(B44:B78,36-COLUMNS($B:B)))