Forum Discussion

meg_89's avatar
meg_89
Copper Contributor
Sep 12, 2022

IF Statements - multiple criteria

I am trying to create IF statements that I can then add conditional formatting to colour code.

This is what I has so far but I cannot figure the rest out. 

=IF(N2>G2,"Over-Min",IF(N2<G2,"Order",IF(P2=N2=G2,"On Order",IF(N2=G2,"Min"))))

I need to compare N2(latest count) to G2(min) but also to the previous count columns. 

So if the latest count is below min I need to "order"

If the latest count is below min but also the same as the last count(P2, R2, T2,V2 and so on) I need it to say "on order"

But if the count and min (N2 and G2) are the same I need it to say "Min"

I am having trouble with it reading min when it should be on-order and vice versa,

Below the status should be "on-order"

MinStatusLATEST COUNTLATEST COUNT DATECOUNTDATECOUNTDATECOUNTDATECOUNTDATE
10Order812-Sep805-Sep1029-Aug1022-Aug1015-Aug

 

excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 12, 2022

    meg_89 

    The expression

    = (P2=N2=G2)

    is very unlikely to be what you require.  Excel evaluates it left to right, first checking to see whether P2=N2 (returns TRUE or FALSE), then tests the result for equality with G2 (a count).  I am rather guessing your requirement, but it may be something like

    = IF(latestCount>Min,"Over-Min",
     IF(latestCount<Min,"Order",
        IF((latestCount=Min),
           IF(MIN(INDEX(datedCounts,{1,3,5,7}))=latestCount,
             "On Order",
             "Min"
           )
        )
     )
  )

Resources