Need help with syntax

[dgardnerman]

I'm trying to add some features to an existing formula so that if G/454= "ALLTHRD" "HDD_STUDS" "THRDD_STUDS" "DEFORMBARS" or "REBAR" are used in the drop down window, the target cell M/454 will display a blank and multiply O/454 by E/454.

Here is my current syntax and link to screenshot:

=IFERROR(IF(A454<>"x","",IF(G454="BOLT","",IF(I454="","",IF(G454="PLATE",(I454*K454/144*L454)*E454,IF(G454="GRATING","",IF(G454="DECKING","",(I454/12*L454)*E454)))))),"")

https://www.dropbox.com/s/r4v9nd4byq679bh/Screen Shot 2018-10-31 at 9.10.33 AM.png?dl=0

 

[mrshl9898]

Not sure what you mean by display a blank AND multiply.

Sounds like an OR statement may help though.

=IF(OR(G454="ALLTHRD",G454="HDD_STUDS",G454="THRDD_STUDS",G454="DEFORMBARS",G454="REBAR"),true,false)

 

[Fluff]

You can also use
=IF(OR(G454={"ALLTHRD","HDD_STUDS","THRDD_STUDS","DEFORMBARS","REBAR"}),TRUE,FALSE)

But this

the target cell M/454 will display a blank and multiply O/454 by E/454.

makes no sense, how can M454 be blank & multiply 2 cells?

 

[dgardnerman]

My shallow thinking was the cell could go about it's business with the original formula, and then we could add more IF to the equation to do something else.
I think I can make it work if I have this formula calculate in a cell separate from the target cell. I'll use an IF in the target cell to calculate the TRUE or FALSE equation.

 

[mrshl9898]

You can add it in there just like your other IFs, just isn't clear what you want the result to be. Just change the TRUE to reflect what you want to happen.

=IFERROR(IF(A454<>"x","",IF(G454="BOLT","",IF(I454="","",IF(G454="PLATE",(I454*K454/144*L454)*E454,IF(G454="GRATING","",IF(G454="DECKING","",IF(OR(G454={"ALLTHRD","HDD_STUDS","THRDD_STUDS","DEFORMBARS","REBAR"}),TRUE,(I454/12*L454)*E454))))))),"")

 

[dgardnerman]

I'm working up a type of chart to help explain my madness...I will post it shortly when I'm done.

 

[dgardnerman]

Here is my spreadsheet of what I'm trying to achieve...I think I have all the info on it.

https://www.dropbox.com/s/pprscju8wqkw91l/Crazy calc.pdf?dl=0

 

[mrshl9898]

Not typing out all the words so you'll need to add to:

=IF(OR(A461="",E461="",M461="",O461=""),"",IF(AND(A461="x",OR(G641 ={"BOLT","DECKING"})),E461*O461,IF(AND(A461="x",OR(G641 ={"ANGLE","BAR"})),(E461*M461)*O461,"")))

 

[dgardnerman]

hmmm...I see the logic when I run through it, but it's returning a blank whether it's from the bolt selection side or the angle selection side. Not sure where the tickler is?

 

[jtakw]

Hi,

This seems more like a follow up to your thread here: https://www.mrexcel.com/forum/excel-questions/1075959-formula-building-post5168728.html#post5168728

But try this, should be all you need:

=IF(AND(A461="X",COUNTA(E461,M461,O461)=3),E461*O461*IF(OR(G461={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"}),M461,1),"")