I have a sheet which calculates the Peak Equity (CS) as well as the Total Equity (CR); both of these eventually wind up being different at some point, as one is calculating from the peak, whilst the other is just the current total.
I have already had some help determining the Total Equity (CR), which works when the sheet is autofiltered, which is great. Both
To calculate the Peak Equity (CS), requires finding the maximum of either the previous Peak Equity (CS) or current Total Equity (CR)
The basic formula is =MAX(CR19,CS18) - with 18 being the first row of actual data. This formula works fine when the sheet is not filtered, but falls over when it is autofiltered, for obvious reasons. Is there a way to have column CS be dynamic, always showing the correct Peak Equity?
Thanks so much in advance
I have already had some help determining the Total Equity (CR), which works when the sheet is autofiltered, which is great. Both
=10000+SUBTOTAL(109,AD$18:AD18) or =AGGREGATE(9,7,AD$18:AD18)+10000 do the job perfectlyTo calculate the Peak Equity (CS), requires finding the maximum of either the previous Peak Equity (CS) or current Total Equity (CR)
The basic formula is =MAX(CR19,CS18) - with 18 being the first row of actual data. This formula works fine when the sheet is not filtered, but falls over when it is autofiltered, for obvious reasons. Is there a way to have column CS be dynamic, always showing the correct Peak Equity?
Thanks so much in advance
