Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasfractions

Using MINIF/MAXIF with strings containing numbers?

I want to find the minimum number with given conditions(is writer and is under probation), the below code works if D contains numbers, but how do I do it if the number is a part of a string, like a fraction for example? Like how do I use this formula if numbers in D look like "1/8", "31/688", "21/33", etc?

=MINIFS(D3:D1007, A3:A1007, "Writer", C3:C1007, "Probation")

I already have another formula that I use that calculates a decimal value given the fraction, If the fraction is in cell D21 then it would look like this:

=left(D21,find("/",D21)-1)/(right(D21,len(D21)-find("/",D21)))

but how do I apply this kind of formula in a minif/maxif?

I have attached a picture to show what I mean, what I'm trying to do is to put a formula in the passed/total column of package stats(probation), and it will get the lowest passed/total value out of the ones with that package name and importance level. as you can see, the entire writer package's pass rate is 5/8 because the lowest pass rate out of the writer package 5/8 is the lowest pass rate out people with package=writers and importance = probation. But at the moment I have to enter the 5/8s manually, I want it to be able to get it automatically using the formula I'm trying to figure out above.

I have attached a picture to show what I mean

Solution

  • try:

    =ARRAYFORMULA(MIN(IF((A3:A="writer")*(C3:C="probation"), 
 IFERROR(REGEXEXTRACT(D3:D, "\d+")/REGEXEXTRACT(D3:D, "/(\d+)"), D3:D), )))

    0

    or to return fraction:

    =ARRAYFORMULA(VLOOKUP(MIN(IF((A3:A="writer")*(C3:C="probation"), 
 IFERROR(REGEXEXTRACT(D3:D, "\d+")/REGEXEXTRACT(D3:D, "/(\d+)"), D3:D), )), 
 {IF((A3:A="writer")*(C3:C="probation"), 
 IFERROR(REGEXEXTRACT(D3:D, "\d+")/REGEXEXTRACT(D3:D, "/(\d+)"), D3:D), ), D3:D}, 2, 0))

    e

    also make sure fractions are formatted as plain text not date