Use of IF(OR(

Sort by date Sort by votes
More info on this ???

just need to be able to turn it on/off with a trigger from A5
Click to expand...
Why didn't post #9 not work ??
 
Last edited:
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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

Code: 
=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

Code: 
=IF(OR(A5="",V5=""),"", V5*W5+X5/60*V5)
 
Upvote 0
Michael M said:
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

Code: 
=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

Code: 
=IF(OR(A5="",V5=""),"", V5*W5+X5/60*V5)
Click to expand...

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.
 
Upvote 0
Okay this should work in Column U

Code: 
=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".
 
Upvote 0
Michael M said:
Okay this should work in Column U

Code: 
=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".
Click to expand...

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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
dgardnerman said:
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
Click to expand...

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
 
Last edited:
Upvote 0
dgardnerman said:
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
Click to expand...


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
131
Denny57
D
E
  • Solved
How to Use IF Statement with WORKDAY function
Replies
5
Views
1K
Fluff
Fluff
I
  • Question Question
Construction Budget distribution using GAMMADIST Function
Replies
4
Views
859
ibrahimdaas
I
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
95
MeaclH
M
N
  • Solved
Conditional Formatting Merged Cells
Replies
9
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,983
Messages
6,175,778
Members
452,668
Latest member
mrider123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top