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

 

[dgardnerman]

Yeah that didn't work, that returned a value of 279 million...

I see what you're doing, you're using either or, no sense in typing out everything if the other value is predictable.

 

[mrshl9898]

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?

I mixed up 641 and 461, had 641 in column G previously.

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

 

[dgardnerman]

I get a blank no matter what I choose

 

[mrshl9898]

Just noticed column M has lbs after the number.

How's this? It assumes it always does.

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

Still doesn't account for bolt and decking not working.... Works fine for me.

 

[mrshl9898]

This will help debug.

=IF(OR(A461="",E461="",M461="",O461=""),"AEMO blank",IF(AND(A461="x",OR(G461 ={"BOLT","DECKING"})),E461*O461,IF(AND(A461="x",OR(G461 ={"ANGLE","BAR"})),(E461*SUBSTITUTE(M461," lbs.",""))*O461,IF(A461<>"x","x not x","other"))))

 

[dgardnerman]

This will help debug.

=IF(OR(A461="",E461="",M461="",O461=""),"AEMO blank",IF(AND(A461="x",OR(G461 ={"BOLT","DECKING"})),E461*O461,IF(AND(A461="x",OR(G461 ={"ANGLE","BAR"})),(E461*SUBSTITUTE(M461," lbs.",""))*O461,IF(A461<>"x","x not x","other"))))

Here is a screenshot with more breakout:

https://www.dropbox.com/s/32rv7ij3i9iykyt/Screen Shot 2018-10-31 at 11.06.41 PM.png?dl=0

Here is the file if you want to play in my dungeon:

https://www.dropbox.com/s/yh0qm7f5rprfyok/test sheet.xlsx?dl=0

I wanted to add the file because it will show you validation setting (if even needed) and VLOOKUP references in case there was a problem in that arena.

 

[dgardnerman]

Here is a screenshot with more breakout:https://www.dropbox.com/s/32rv7ij3i9iykyt/Screen Shot 2018-10-31 at 11.06.41 PM.png?dl=0Here is the file if you want to play in my dungeon:https://www.dropbox.com/s/yh0qm7f5rprfyok/test sheet.xlsx?dl=0I wanted to add the file because it will show you validation setting (if even needed) and VLOOKUP references in case there was a problem in that arena.

still working through this...had to edit the post...

 

[dgardnerman]

Here is a screenshot with more breakout:

https://www.dropbox.com/s/32rv7ij3i9iykyt/Screen Shot 2018-10-31 at 11.06.41 PM.png?dl=0

Here is the file if you want to play in my dungeon:

https://www.dropbox.com/s/yh0qm7f5rprfyok/test sheet.xlsx?dl=0

I wanted to add the file because it will show you validation setting (if even needed) and VLOOKUP references in case there was a problem in that arena.

I think I got it by changing the opening syntax from:
=IF(OR(A461="",E461="",M461="",O461="")
=IF(OR(A475="",E475<0,M475<0,N475="")
(btw...N475 is the old O461 shown above, I deleted column N as it was not tied to anything and was not needed)

Now the only issue is if I change the drop down in column G (TYPE), the VLOOKUP function that reports to column H (DESCRIPTION) defaults column M to a blank which returns column P (TOTAL) to #VALUE .

Not a show stopper by any means, it just forces the hand to input the correct description so the calculation can complete itself.
I would like to see the #VALUE go away though.
My thought is that we need an IFERROR statement put in somewhere to get rid of the #VALUE , I just don't know where to put it.

any thoughts?

 

[jtakw]

My formula from Post # 10 is working correctly for your sample file in Post # 16, provided there's a value in O460

 

[dgardnerman]

True, it works fine, however the drop down menu TYPE in column G dictates the type of calculation, and the formula in post 10 only has direction for 7 of the 16 TYPE's.
It's an either or situation wheras:
{"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"} will multiply; N324*O324
and
{"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd","rebar"} will multiply; F324*O324

Sorry, I have been adding and deleting some columns as I construct so the formulas have been changing slightly.