Hello
I have a table in sheet1 with manual data in A column and B column formula calculates maximum retrace in A column and ignored hidden rows. formula in column B is true.
but please look at sheet2 I add manual column which is column A
in the column B I used SUBTOTAL,109 for auto sum column A with ignore hided rows
now in the column C formula is not work
I think my Array formula reads manual data but I need that works with table in sheet2
Would so appreciate help
The support page for the SUBTOTAL function is clear about that.
"If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting."
In Sheet 2 you reference SUBTOTAL(109, ...) to a column that consists of formulas that all are SUBTOTALS. All are ignored, a zero value is returned.
By the way, I believe you can simplify your formula for MAX DD% to:
=SUBTOTAL(105,[Total R2])-[@[Total R2]]
I added this formula as an alternative. It works in Sheet1 but, obviously not in Sheet2 due what I explained above.
in the sheet 1 please hide row 15 which is last row. true result is 0 but SUBTOTAL,105 returns -2.00%. I can't understand it.
for the main question is it possible with helper column such as AGGREGATE function or anything else ?
I appreciate of your time and your knowledge
I assume you refer to the -2% that shows up in C14. Why should that be zero?
Thanks for your time
after see the attached file please read below notes :
in the table if column A maked by hand, I don't have any problem to calculate maximum drowdown. but its automated and its eqaul column B in the sheet3 which is maked by SUBTOTAL,109. and arrey formula does not work with SUBTOTAL function.
I hope my words be clear
If there is a problem in my words, please let me explain more
Regards best.
Then I just misunderstood your intentions. Forget my suggested formula change since your own seems to work for you.
Going back to your original question, the real issue was why the formula doesn't work in the original Sheet2. I believe I explained that in my first answer.
Hello
Thank you for your time
my question is solved with VALUE function in helper column
I attached solved file
