Max formula ignoring N/A values
sisco
-
Anonymous user -
Anonymous user -
Hello everyone,
I would like to incorporate an "if error" into my formula (below) because when there is a variable set to "N/A," my Max() formula returns a "N/A" whereas I want my formula to take the highest value excluding the "N/A." For example:
A1 = 7
A2 = 2
A3 = N/A
A4 = 4
with my Max() formula, I end up with "N/A," however I would like to get "7."
Formula: =MAX(IF(AA5=3,OFFSET(INDEX(Ref.!K11:P16,MATCH(K5,Ref.!G11:G16,0),MATCH(L5,Ref.!K10:P10,0)),3,-3),IF(AA5=2,OFFSET(INDEX(Ref.!K11:P16,MATCH(K5,Ref.!G11:G16,0),MATCH(L5,Ref.!K10:P10,0)),2,-2),IF(AA5=1,OFFSET(INDEX(Ref.!K11:P16,MATCH(K5,Ref.!G11:G16,0),MATCH(L5,Ref.!K10:P10,0)),1,-1),IF(AA5=0,M5,"N/A")))),IF(AA5=3,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(S5,Ref.!K30:P30,0)),3,-3),IF(AA5=2,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(S5,Ref.!K30:P30,0)),2,-2),IF(AA5=1,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(S5,Ref.!K30:P30,0)),1,-1),IF(AA5=0,T5,"N/A")))),IF(AA5=3,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(V5,Ref.!K30:P30,0)),3,-3),IF(AA5=2,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(V5,Ref.!K30:P30,0)),2,-2),IF(AA5=1,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(V5,Ref.!K30:P30,0)),1,-1),IF(AA5=0,W5,"N/A")))),IF(AA5=3,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(P5,Ref.!K30:P30,0)),3,-3),IF(AA5=2,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(P5,Ref.!K30:P30,0)),2,-2),IF(AA5=1,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(P5,Ref.!K30:P30,0)),1,-1),IF(AA5=0,Q5,"N/A")))))
Thank you in advance for your help.
PS: sorry for the lengthy formula, it considers several conditions and since I'm not great at VBA, I'm managing with formulas.
Configuration: Windows / Chrome 94.0.4606.81
I would like to incorporate an "if error" into my formula (below) because when there is a variable set to "N/A," my Max() formula returns a "N/A" whereas I want my formula to take the highest value excluding the "N/A." For example:
A1 = 7
A2 = 2
A3 = N/A
A4 = 4
with my Max() formula, I end up with "N/A," however I would like to get "7."
Formula: =MAX(IF(AA5=3,OFFSET(INDEX(Ref.!K11:P16,MATCH(K5,Ref.!G11:G16,0),MATCH(L5,Ref.!K10:P10,0)),3,-3),IF(AA5=2,OFFSET(INDEX(Ref.!K11:P16,MATCH(K5,Ref.!G11:G16,0),MATCH(L5,Ref.!K10:P10,0)),2,-2),IF(AA5=1,OFFSET(INDEX(Ref.!K11:P16,MATCH(K5,Ref.!G11:G16,0),MATCH(L5,Ref.!K10:P10,0)),1,-1),IF(AA5=0,M5,"N/A")))),IF(AA5=3,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(S5,Ref.!K30:P30,0)),3,-3),IF(AA5=2,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(S5,Ref.!K30:P30,0)),2,-2),IF(AA5=1,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(S5,Ref.!K30:P30,0)),1,-1),IF(AA5=0,T5,"N/A")))),IF(AA5=3,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(V5,Ref.!K30:P30,0)),3,-3),IF(AA5=2,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(V5,Ref.!K30:P30,0)),2,-2),IF(AA5=1,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(V5,Ref.!K30:P30,0)),1,-1),IF(AA5=0,W5,"N/A")))),IF(AA5=3,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(P5,Ref.!K30:P30,0)),3,-3),IF(AA5=2,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(P5,Ref.!K30:P30,0)),2,-2),IF(AA5=1,OFFSET(INDEX(Ref.!K31:P36,MATCH(K5,Ref.!G31:G36,0),MATCH(P5,Ref.!K30:P30,0)),1,-1),IF(AA5=0,Q5,"N/A")))))
Thank you in advance for your help.
PS: sorry for the lengthy formula, it considers several conditions and since I'm not great at VBA, I'm managing with formulas.
Configuration: Windows / Chrome 94.0.4606.81
3 answers
-
Good evening,
Another proposal applied to your example (range A1:A4)
=MAXIFS(A1:A4,A1:A4,"<>#N/A")
The range A1:A4 is indeed repeated twice
The MAXIFS function is not available in all versions of Excel: https://support.microsoft.com/en-us/office/max-ifs-max-ifs-function-dfd611e6-da2c-488a-919b-9b6376b28883?ns=excel&version=19&syslcid=1033&uilcid=1033&appver=xls190&helpid=xlmain11.chm60651&ui=en-us&rs=en-us&ad=en -
Hello everyone,
Hello,
Generally, with a "truckload" of IF(), there is surely another method to achieve the desired result.
An EXCEL file (test or not), completed with exhaustive explanations and manually filled examples, uploaded to https://www.cjoint.com/, would allow the participants to answer your question more precisely. Also include your version of Excel.
Best regards-
Hello,
It's true that it's unreadable; for my part, I didn't even try to understand it.
So just a general rule that is often misunderstood: even if we "condition" N/A in the formula, we'll be stuck as soon as Excel processes it and encounters it.
IF(AA5=0,M5,"N/A"): the N/A error results not from the fulfillment of the negative condition, but from the prior "calculation" of AA5.
We need to prevent Excel from doing this calculation by testing N/A "at the beginning":
IF(ISNA(AA5), etc...
-
-
Hello,
Somewhat random proposal (without a file)
Replace ;« N/A ») with ;-99)
-99 is a lower bound for all values.
If AA5 can take other values than 0, 1, 2, or 3
=IF(OR(AA5=0;AA5=1;AA5=2;AA5=3);MAX(the modified formula)))));NA())
