AND or OR fuction - or maybe something else

N

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
I've been struggling with this problem for way to long and thought it's time to get Help.

With the below, I'm okay:
BID

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 172px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">93</TD><TD style="FONT-SIZE: 8pt">CAULKING</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt">109.1</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">5%</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt">1.9</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">TUBES</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">97</TD><TD style="FONT-SIZE: 8pt">NP1 CAULKING</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt">109.1</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">5%</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt">1.3</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">TUBES</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">99</TD><TD style="FONT-SIZE: 8pt">CAULKING RAIN BUSTER</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">5%</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">TUBES</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D93</TD><TD>=IF(N210=FALSE,C18,0)</TD></TR><TR><TD>F93</TD><TD>=ROUNDUP((D93/60)*(1+E93),2)</TD></TR><TR><TD>D97</TD><TD>=IF(N210=FALSE,C18,0)</TD></TR><TR><TD>F97</TD><TD>=ROUNDUP(D97/90*(1+E97),2)</TD></TR><TR><TD>D99</TD><TD>=IF(N210=TRUE,C18,0)</TD></TR><TR><TD>F99</TD><TD>=ROUNDUP(D99/30*(1+E99),2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I then have Check boxes that return True or False in Column N. When I Check the box for Rain Buster Caulk, everyting still works fine. As shown here:

BID

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 172px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">93</TD><TD style="FONT-SIZE: 8pt">CAULKING</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">5%</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">TUBES</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">97</TD><TD style="FONT-SIZE: 8pt">NP1 CAULKING</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">5%</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">TUBES</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">99</TD><TD style="FONT-SIZE: 8pt">CAULKING RAIN BUSTER</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt">109.1</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">5%</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 8pt">3.8</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">TUBES</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D93</TD><TD>=IF(N210=FALSE,C18,0)</TD></TR><TR><TD>F93</TD><TD>=ROUNDUP((D93/60)*(1+E93),2)</TD></TR><TR><TD>D97</TD><TD>=IF(N210=FALSE,C18,0)</TD></TR><TR><TD>F97</TD><TD>=ROUNDUP(D97/90*(1+E97),2)</TD></TR><TR><TD>D99</TD><TD>=IF(N210=TRUE,C18,0)</TD></TR><TR><TD>F99</TD><TD>=ROUNDUP(D99/30*(1+E99),2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I have another Check box that I may have to check to bring in If(N211=TRUE,I13,0). But I still need what is currently in D93 to do it's test.

In other words, if N210=TRUE return 0, but also look at N211 and if TRUE bring in I13.

N210 changes to to T or F depending on if I check Rain Buster.

I hope I explaind well enough. Spent hours just thinking How to do it, and then How to explain it.

Thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
After rereading my post, I see that I forgot another part to the Test.

In D93, if N210 = FALSE bring in C18 and if N211=TRUE also bring in I13. Along with the test I posted above.
 
Upvote 0
Sorry struggling to get what you are doing

Can you explain in a simple table what you want D93 to return - something like:

Value in N210 Value in N211 Value Answer
True True ????
True False ????
False False ????
False True ????

Add in any other conditions in the same way
 
Upvote 0
I got it working. But it seems rather long formula.

=IF(AND(N210=FALSE,N211=TRUE),SUM(C18+I13),IF(AND(N210=TRUE,N211=TRUE),I13,IF(N210=FALSE,C18,0)))

If somebody sees something I could do different, I'd like to see it.

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,609
Messages
6,179,882
Members
452,948
Latest member
Dupuhini

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