Use of IF(OR(

[dgardnerman]

I might have my wires crossed on syntax...
I am trying to get my target cell to function but I get a blank.


=IF(OR(V5="",W5="",X5=""),"",(V5*W5+X5/60*V5))

https://www.dropbox.com/s/1nlp4h1a783mytx/Screen Shot 2018-11-02 at 4.25.29 PM.png?dl=0

 

[Michael M]

More info on this ???

just need to be able to turn it on/off with a trigger from A5

Why didn't post #9 not work ??

 

[dgardnerman]

More info on this ???


Why didn't post #9 not work ??

Not sue, you can d/l my spreadsheet and take a peek. You won't be able to use/see any of the dropdown if you have objects turned off.

https://www.dropbox.com/s/mmhijtrdal5omlj/FT-IN-TIME.xlsx?dl=0

It's pretty simple theory I use, yellow requires input, purple is a calculation.

 

[Michael M]

The formula on the Estimate sheet in Col U needs an extra criteria for starters.....
You need to ensure that ALL errors are taken care of before moving fwd on other formulas

=IF(OR(E8="",S8="",[color=red]R8=""[/color]),"",IF(AND(A8="x",OR(G8={"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd"})),E8*S8,IF(AND(A8="x",OR(G8={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"})),R8*S8,"")))

Also, I can't see why this wouldn't work, including the trigger

=IF(OR(A5="",V5=""),"", V5*W5+X5/60*V5)

 

[dgardnerman]

The formula on the Estimate sheet in Col U needs an extra criteria for starters.....
You need to ensure that ALL errors are taken care of before moving fwd on other formulas

=IF(OR(E8="",S8="",[COLOR=red]R8=""[/COLOR]),"",IF(AND(A8="x",OR(G8={"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd"})),E8*S8,IF(AND(A8="x",OR(G8={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"})),R8*S8,"")))

Also, I can't see why this wouldn't work, including the trigger

=IF(OR(A5="",V5=""),"", V5*W5+X5/60*V5)

You just hit what appears to be my last snag...
col U has had a few people people stumped. change the drop down menu col G to plug, bolt, decking, etc... I cannot get the equation to multiply col E* col R, everything else works fine.

 

[Michael M]

Okay this should work in Column U

=IF(OR(E5="",R5="",S5=""),"",IF(AND(A5="x",LOOKUP(G5,{"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd"},{1,1,1,1,1,1,1,1,1})),E5*S5,IF(AND(A5="x",LOOKUP(G5,{"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"},{1,1,1,1,1,1,1})),(R5*S5),"")))

BUT...most of the time the formula WON'T work because Column "R" is TEXT !!, thereby giving a formula error in "U".

 

[dgardnerman]

Okay this should work in Column U

=IF(OR(E5="",R5="",S5=""),"",IF(AND(A5="x",LOOKUP(G5,{"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd"},{1,1,1,1,1,1,1,1,1})),E5*S5,IF(AND(A5="x",LOOKUP(G5,{"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"},{1,1,1,1,1,1,1})),(R5*S5),"")))

BUT...most of the time the formula WON'T work because Column "R" is TEXT !!, thereby giving a formula error in "U".

Interesting...let me find the post where I got it to work before but had another wonky error...
#18

Also, not sure what you mean by column R is text? It's a formatted cell with a custom prefix.

Look at the latest and greatest worksheet with everything working but BOLT, etc

https://www.dropbox.com/s/mmhijtrdal5omlj/FT-IN-TIME.xlsx?dl=0

 

[Michael M]

Just as a suggestion....are you aware of Formula Evaluation ??
If you put the select a cell with a formula in it, then Under the formula tab in Formula Auditing select Evaluate Formula.
This will then go through a formula one step at a time AND show the result of that step, so errors can be narrowed down to certain locations.

 

[dgardnerman]

I have tried to play in the formula builder with nothing fruitful to show.I know just enough to get into trouble and that's why I resort to this awesome site.
If I haven't said it...Thank you very much for your help so far!
I hope in a few years I'm the one helping out on this forum.

 

[dgardnerman]

Interesting...let me find the post where I got it to work before but had another wonky error...
#18

Also, not sure what you mean by column R is text? It's a formatted cell with a custom prefix.

Look at the latest and greatest worksheet with everything working but BOLT, etc

https://www.dropbox.com/s/mmhijtrdal5omlj/FT-IN-TIME.xlsx?dl=0

I got it to work! (mostly)... here is the formula I was using:

=IF(OR(E30="",R30="",S30=""),"",IF(AND(A30="x",OR(G30={"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd","rebar"})),E30*S30,IF(AND(A30="x",OR(G30={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"})),(R30*S30),"")))

here is the formula I changed it to:

=IF(AND(E24="",R24="",S24=""),"",IF(AND(A24="x",OR(G24={"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd","rebar"})),E24*S24,IF(AND(A24="x",OR(G24={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"})),(R24*S24),"")))

I changed the opening syntax from: =IF(OR( to =IF(AND(

One catch... if you change the TYPE (col G) to use the "Angle", "Bar", "Beam", etc...part of the formula, and you don't change the DESCRIPTION (col H) so the INDIRECT cell validation pulls in the right parameters, you will get a #VALUE .

Is there a way to workaround the #VALUE with an IFERROR to make that go away?

Latest & greatest spreadsheet here (and with a lot more insight, still work in progress):

https://www.dropbox.com/s/mmhijtrdal5omlj/FT-IN-TIME.xlsx?dl=0

 

[dgardnerman]

I got it to work! (mostly)... here is the formula I was using:

=IF(OR(E30="",R30="",S30=""),"",IF(AND(A30="x",OR(G30={"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd","rebar"})),E30*S30,IF(AND(A30="x",OR(G30={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"})),(R30*S30),"")))

here is the formula I changed it to:

=IF(AND(E24="",R24="",S24=""),"",IF(AND(A24="x",OR(G24={"Plug","bolt","decking","grating","thrdd_studs","hdd_studs","deformbars","allthrd","rebar"})),E24*S24,IF(AND(A24="x",OR(G24={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"})),(R24*S24),"")))

I changed the opening syntax from: =IF(OR( to =IF(AND(

One catch... if you change the TYPE (col G) to use the "Angle", "Bar", "Beam", etc...part of the formula, and you don't change the DESCRIPTION (col H) so the INDIRECT cell validation pulls in the right parameters, you will get a #VALUE .

Is there a way to workaround the #VALUE with an IFERROR to make that go away?

Latest & greatest spreadsheet here (and with a lot more insight, still work in progress):

https://www.dropbox.com/s/mmhijtrdal5omlj/FT-IN-TIME.xlsx?dl=0

I figured it out!

I added IFERROR to the opening syntax and closed with ,"") and my spreadsheet is perfect!

Thanks again for dealing with my limited skillset.