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
—–
Bill
One way to do it is to create another column (you can hide these columns) and then use a very large number instead of 0 for the result if column C is true. Then use the MIN function on that so you get the minimum value because all of the non-trading days are 9999999 or whatever.
Another solution is to simply delete the rows that are non-trading days. Why have those at all?
You could also use a formula with the SMALL function instead of the MIN function and then count the number of 0s (plus one). So like this:
SMALL(D,COUNTIF(D,0)+1)
What this does is it gets the number of 0s in column D and adds one to it. Then SMALL will give you that nth smallest number, which corresponds to the smallest number after the 0s.
A few other things. Why do you use SUMIF in column D? Using IF will get you the same result and is more straightforward. And also use 0 if you want a 0 instead of FALSE.
IF(C3,0,B3)
So, basically, if C3 is true, then 0, else the value of B3.
If you are looking to slim things down, there is no need for column B. You can just use this in column D (and then move things over).
IF(ISERROR(B3), 0, B3)
I'm confused as to why you have the date split into three cells and spread out over A2, B2 and A3. It would make a lot more sense to simple have the date in column A and that's it. Does this mean you don't have one date on one row? I'm confused about this.
BTW, if you want a good way to calculate only weekdays, use this:
A2+1+IF(WEEKDAY(A2+1,3)>4,2,0)
So A2 would be the first weekday in your table. Then A3 would use this formula which would add one to the day, and 2 more to the day if the day is Saturday so it would jump over weekends.
Many thanks for looking at my query, I looked at your suggestions and found that one suggestion saved 12 columns.
IFERROR(STOCKH($A$1,0,DATE($A$2,B$2,$A23)),"") in the first column worked for me today, didn't yesterday for some reason and now saving me 36 columns.
Many Thanks