Relative scales

[yasarayhanka]

I am trying to get the scales of some data to each other

my ranges are relative and the number of the items will be included in the scale are relative as well

here is a sample

a2660.100415b4680.17667c7860.296716d6480.244621e4810.181578 Grand 126491f449g1000h1569i1370j1051 Grand 25439k39l38m66n49 Grand 3192
(please refer to the attachment for a clear pic.)
I need to get each items ratio to the grand that it belongs to ie (a/grand 1, b/grand 1 ) and I need to do this for all the ranges underneath which are relavent.

Thanks for the help in advance.

yasar

[Bob Phillips]

In C1

=B1/INDEX(B1:$B$30,MIN(IF(A1:$A$30="Grand",ROW(B1:$B$30)))-MIN(ROW(B1:$B$30))+1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[mdmackillop]

You could use a UDF (UserDefinedFunction)

[vba]Function Ratio(Data)
Application.Volatile
If Data = "" Then
Ratio = 0
Exit Function
End If
If Data.Offset(1) = "" Then
Ratio = Data / Data
Else
Ratio = Data / Data.End(xlDown)
End If
End Function[/vba]

[yasarayhanka]

In C1

=B1/INDEX(B1:$B$30,MIN(IF(A1:$A$30="Grand",ROW(B1:$B$30)))-MIN(ROW(B1:$B$30))+1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

Is there a way to do this through a VB code, I would rather use relative ranges bc the range is going to change every week.

thanks,
yasar

[Bob Phillips]

Just set $B$30 etc higher than you will ever use.