Rangesum showing only at year end

[mikecherry]

Hi all,

I am trying to use rangesum and I want to sum just what's happened during that year for the column called "Preferred Returns".

I want also to show that amount in another column (Called "Preferred Returns" too) against the 31/12 of each year.

This is the formula I am using for the circled section (See the picture below):

above(Rangesum(above(sum(Calls+[Gross Investment Distributions]),0,rowno())),1)*$(vPrefReturnRequired)*((Date#(Date)-above(Date#(Date)))/366)

Attached you can find the app. 

Please let me know if you can help me with this.

Thanks

[sunny_talwar]

May be this

if(left(Date,5)='31/12',
above(Rangesum(above(sum(Calls+[Gross Investment Distributions]),0,rowno())),1)*$(vPrefReturnRequired)*((Date#(Date)-above(Date#(Date)))/366), 0)

[mikecherry]

Nope I want to show the Rangesum of that expression against that cell in this way it's only returning the value that is against that cell

[sunny_talwar]

I am not sure I follow... what would you want to see here if not -29,270?

[mikecherry]

That should be the sum of all the preferred returns for the year (-98,511).

The second one against 31/12/2013 should be around -441,949 ish

[sunny_talwar · Accepted solution]

May be this

Aggr(If(Left(Date, 5) = '31/12', RangeSum(Above(
Above(TOTAL Rangesum(Above(TOTAL Sum(Calls+[Gross Investment Distributions]), 0, RowNo(TOTAL))), 1) * $(vPrefReturnRequired)*((Date#(Date)-Above(TOTAL Date#(Date)))/366), 0, RowNo(TOTAL))), 0), Year, Date)

[mikecherry]

Sunny Talwar my hero as always !!!!

Thank you so much !

[mikecherry]

Hi Sunny,

It's me again. I am actually trying to achieve the same table here in the excel spreadsheet attached.

Do you think you can help ?

[sunny_talwar]

Does this has to be done on the front end? Can we do this in the script?

[mikecherry]

Yes we can !