Forum Discussion
formula help
You wrote: Help? (I added the color for emphasis)
Here's the best help I can offer:
Don't spend any more time attempting to make this monstrosity of a formula work.
Let's assume for a moment that you were able to make it work. What will happen next year when you have to add a different state, or a new airport? The fact of the matter is that this formula could be a case study in why multiple nested IF and IFS functions carry heavy warnings in every introductory text on Excel. Even when they work when first designed--miraculous enough--they are nightmares to maintain, because they're next to unintelligible, even to the person who got lucky making it work the first time.
So what to do: well, you say that each component works separately. Leave them as separate functions, each in its own column (or row, depending on how the spreadsheet itself is arrayed), with one final that takes the results of the separate and constructs the final. That is, don't try to nest them so deeply into a single formula; it might be fun, but (well, refer back to the preceding paragraph)...
Another solution potentially: it often is the case that, instead of using conditions in an IF formulation, one can create a table of the various possible combinations and use VLOOKUP or INDEX and MATCH to select the row/cell that is relevant. Doing that enables easier maintenance if/when (for example) a new airport gets added, or one gets eliminated...
mathetes As usual I concur with Mathletes that this beast needs to be tamed. using lookups or even LET and then defining components could help. That said, it does appear broken:
=IF(
IFS(AD2="S",INFO!$F$2,'NH Import'!AS2="CA",INFO!$F$4,'NH Import'!AS2="CO",INFO!$F$7,'NH Import'!AS2="MA",INFO!$F$9),
( IFS( AND(AD2="H",AS2<>"NV"),INFO!$F$3),
( IFS(AD2="S",INFO!$F$2,'NH Import'!AS2="CA",INFO!$F$4,'NH Import'!AS2="CO",INFO!$F$7,'NH Import'!AS2="MA",INFO!$F$9),
( IFS(AD2="S",INFO!$F$2,'NH Import'!AS2="CA",INFO!$F$4,'NH Import'!AS2="CO",INFO!$F$7,'NH Import'!AS2="MA",INFO!$F$9),
( IF( AND(AA2="FT",(OR(Monster!Y2="LGA",Monster!Y2="JFK",Monster!Y2="EWR")) ),
INFO!F8,
INFO!$F$10),
( IF( AND(AD2="H",AS2="NV",AB2>=14.63), INFO!F3, INFO!F5) )
)
)
)
)
)so the line 2 is the original IF conditional and all inside its own IFS()
The 3rd line starts the TRUE parameter of the IF which starts with a "(" then has an IFS and then has a "," that is the first ERROR and then multiple similar errors of completing functions and having a "," after it
I have no idea which should have been inside an AND / OR or which were SUPPOSED to be part of the TRUE/FALSE of another IF.
I also noticed that lines 2, 4 and 5 appear identical so a perfect use for LET()