Apple Numbers 6.1 / IMAC
I have a spreadsheet showing daily share prices over a year with a dropdown to change companies and the year. Trying to get monthly MIN/MAX but the column has “Ref” on a non-trading day.
I had to use 4 columns of formulas before getting a result, is there a formula I can use to achieve the result using fewer columns.
Column B : STOCKH($A$1,0,DATE($A$2,B$2,$A3)) *Result as expected with Ref on non-trading days. A1=Company, A2=Year, B2=Month, A3=Day
Column C : ISERROR(B3) *Now I have “True” on non-trading days
Column D : SUMIF(C3,”FALSE”,B3) *All good, now I have “0” instead of Ref, now I can get the MAX result but with many zeros, of course, my MIN is always going to be zero.
Column E : IF(D3=0,””,D3) *Now my goal is reached but I now have 49 columns calculating formulas, which I feel is slowing down the spreadsheet when I change the company or date.
I would be obliged for any suggestions …. Many Thanks