Apr 26 2022 02:39 PM - edited Apr 26 2022 02:40 PM
TRYING TO ADD ONE MORE CONDITION "(IF($I2="CONC W REBAR",($O2)," TO THIS FORMULA
=IF($H2="cw",(IF($I2="ASPHALT",($O2),(IF($I2="BRICKS",($O2),(IF($I2="CONCRETE",($O2),(IF($I2="DIRT/GRAVEL",($O2),(""))))))))),(""))
WHEN I ADD IT THE I GET THE ERROR MESSAGE "YOU'VE ENTERED TOO MANY ARGUMENTS FOR THIS FUNCTION"
Apr 26 2022 02:56 PM
Solution=IF($H2="cw",IF($I2="ASPHALT",$O2,IF($I2="BRICKS",$O2,IF($I2="CONCRETE",$O2,IF($I2="DIRT/GRAVEL",$O2,IF($I2="CONC W REBAR",$O2,""))))),"")
Does this work in your sheet? I removed some " ( " and " ) " which makes the formula easier in my opinion.
Apr 26 2022 03:36 PM
It is also possible to reformulate the formula to reduce the level of nesting,
=IF(code="cw",
SWITCH(material,
"ASPHALT", return,
"BRICKS", return,
"CONCRETE", return,
"DIRT/GRAVEL", return,
"CONC W REBAR", return,
""),
"")
or
= IF(code="cw",
IF(
OR(material={"ASPHALT","BRICKS","CONCRETE","DIRT/GRAVEL","CONC W REBAR"}),
return,
""),
"")
The latter would require a Lambda helper function if it were to be used as an array formula over 'code' and 'material'.
May 03 2022 07:31 AM
Apr 26 2022 02:56 PM
Solution=IF($H2="cw",IF($I2="ASPHALT",$O2,IF($I2="BRICKS",$O2,IF($I2="CONCRETE",$O2,IF($I2="DIRT/GRAVEL",$O2,IF($I2="CONC W REBAR",$O2,""))))),"")
Does this work in your sheet? I removed some " ( " and " ) " which makes the formula easier in my opinion.