Feb 23 2019 03:49 PM
Good afternoon all, I have developed a spreadsheet to be used for several users with different excel versions and not being able to update to latest one. In this spreadsheet I have several MINIFS, MAXIFS to substract info from a large set of data. The MINIFS, MAXIFS formulas are located in other sheets that summarize the values in the first data set. In some of the users computers these formulas don´t work, I´m trying to find a workaround to develop same MINIFS, MAXIFS results without being necessary to use those formulas, but haven´t been able to.
This is my original formula:
MAXIFS(SS!$C$18:$C$3001,SS!$A$18:$A$3001,I5)
I´ve tried this workaround:
MAX(IF(SS!A18:A3001=I5,SS!C18:C3001,""))
And it returns a #VALUE error, can you please help me solve this? I believe if I find the way to make it work with MAXIFS, I´ll be able to make it work with MINIFS. If this helps, I have a cell format DATE in C column data.
I would really appreciate your help,
Regards,
Laura
Feb 23 2019 05:07 PM
Feb 23 2019 05:35 PM
Feb 23 2019 06:16 PM
Feb 23 2019 07:40 PM
Hello again!!! Thanks for the quick reply.
My column C in the sheet SS has Date data, each row is being calculated from previous rows. I found the problem, C column formula not only returns a number but also "", since the column has both numbers and "" results, that´s where the #VALUE appears.
I eliminated that "" conditional in the formulas in column C and your workaround is OK to replace MAXIFS, but not for MINIFS.
For MINIFS I used:
=INDEX(SS!$C$18:$C$3001,MATCH(I5,SS!$A$18:$A$3001,0))
It worked, eliminating the "" values both formulas worked fine.
Thanks again!
Laura
Feb 23 2019 10:19 PM
Feb 24 2019 08:50 AM
I'd prefer for MAX
=AGGREGATE(14,6,1/(SS!$A$18:$A$3001=I5)*SS!$C$18:$C$3001,1)
and for MIN
=AGGREGATE(15,6,1/(SS!$A$18:$A$3001=I5)*SS!$C$18:$C$3001,1)