Summation Notation

[mekwsy]

Hello,

if i have an array likes {60,72,83,90,92} and I would like to calculate the below equation,

(60)^2.5+(60+72)^2.5+(60+72+83)^2.5+(60+72+83+90)^2.5+(60+72+83+90+92)^2.5

How can I do it without using VB??

Please help!!

Many thanks!

Karena

 

[jasonb75]

I think this works.

=SUM(SUBTOTAL(9,OFFSET(INDEX(B:B,MATCH(1E+100,B:B)),,,-SMALL(IF(ISNUMBER(B2:B13),MATCH(1E+100,B:B)-ROW(B2:B13)+1),ROW(INDIRECT("1:"&COUNT(B2:B13))))))^2.5)

CSE array confirmed.

I'm wondering if "Base" is always the last entry in the column, if so it might be a better match criteria.

That is thinking ahead slightly, I've also been looking at Aconcat to return the array string that the OP asked for (32,35,37,38,40,42).

{=SUBSTITUTE(TRIM(ACONCAT(IF(B2:INDEX(B:B,MATCH("Base",B:B,0))<>"",A2:INDEX(A:A,MATCH("Base",B:B,0)),"")," "))," ",",")}

But that returns the array in the reverse order, looking for an easy way to reverse it.

Any reason why you use INDEX(B:B,1) instead of directly entering B$1 ?

 

[pgc01]

I think this works.

Yes, I think it does work ok.

There is a difference between our formulas in the values of the Offset() parameters. You use in the 4th parameter, Height, a negative value.

That use of a negative value in the Offset() height, in cases like this one, makes formulas smaller. In my formula, to use a positive value I must use 2 times the Row(Indirect()) which makes the formula much bigger. The reason I don't use it anymore (I used to use it) is because ms published an entry in the knowledge base saying that this is an incorrect behaviour (entry 184109):

http://support.microsoft.com/kb/184109

As I said the formulas usually work with the negative height, I'm just being cautious.

That is thinking ahead slightly, I've also been looking at Aconcat to return the array string that the OP asked

Yes, as I said you can use code to do it.

Any reason why you use INDEX(B:B,1) instead of directly entering B$1 ?

I guess it's also me being too cautions again. If you use B$1, if someone inserts rows at the beginning the address will change and may rend the formula incorrect. INDEX(B:B,1) is indifferent to row insertions.

 

[jasonb75]

There is a difference between our formulas in the values of the Offset() parameters. You use in the 4th parameter, Height, a negative value.

That use of a negative value in the Offset() height, in cases like this one, makes formulas smaller. In my formula, to use a positive value I must use 2 times the Row(Indirect()) which makes the formula much bigger. The reason I don't use it anymore (I used to use it) is because ms published an entry in the knowledge base saying that this is an incorrect behaviour (entry 184109):

http://support.microsoft.com/kb/184109

As I said the formulas usually work with the negative height, I'm just being cautious.

It's probably just been declared as incorrect behaviour because MS didn't intend it to work in that way

Hopefully they have realised that it's more useful in its 'broken' form and won't try fixing it any time soon.

I just used index as an alternative to indirect in another thread, and even then it didn't register that you were using it in the same context, as a non-volatile alternative. I'm learning - slowly.

 

[pgc01]

So am I!

 

[jasonb75]

This isn't a very elegant method, but it appears to work.

Note that the named range used is based on the formula being entered at the foot of the source data, and the end of the source data being flagged by a cell holding "Base", so will need amending based on actual layout.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">43</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">92</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">42</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">41</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">40</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">60</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">39</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">38</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">83</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">37</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">72</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">36</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">35</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">60</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">33</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">32</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Base</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>Result</TD><TD style="TEXT-ALIGN: right">4740232.343</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Array</TD><TD style="TEXT-ALIGN: right">32,35,37,38,40,43</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B16</TD><TD>{=SUM(SUBTOTAL(9,OFFSET(INDEX(bRange,ROWS(bRange)),,,-SMALL(IF(ISNUMBER(bRange),ROWS(bRange)-ROW(bRange)+1),ROW(INDIRECT("1:"&COUNT(bRange))))))^2.5)}</TD></TR><TR><TD>B17</TD><TD>{=aCONCAT(N(OFFSET(INDEX($A:$A,ROWS(bRange)),-SMALL(IF(bRange<>"",ROWS(bRange)-ROW(bRange)),ROW(INDIRECT("1:"&COUNTA(bRange)))),0)),",")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR><TR><TD><TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Names in Formulas </TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD></TD><TD>Name</TD><TD>Applies to</TD></TR><TR><TD></TD><TD>bRange</TD><TD>=OFFSET(INDIRECT("R1C",0),,,MATCH("Base",INDIRECT("C:C",0),0))</TD></TR><TR><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
 
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
 
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[mekwsy]

Hi Jason,

Is it means that i have to copy the above code into my excel?

I havent did this before. Can you help me more!

great thanks.

Karena

 

[jasonb75]

Yes, you do need to copy the code.

First open your excel workbook, then press Alt + F11
From the editor menu select Insert > Module
Then paste the code there and close the editor and save, (saving in excel will save the code as well).