Need AND OR Formula Help

samsilverman · Dec 2, 2009

I need help writing a formula for this scenario for Excel 2007:

<link rel="File-List" href="file:///C:%5CUsers%5CSam%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CSam%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CSam%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> IF L17 <=-.01 and M17 and L18 are blank, then ""
OR
IF L17 <=-.01 and L18 <=-.0001 and M17 is blank, then ""
OR
IF L17 <=-.01 and M17 <=-.01 and L18 <=-.0001, then ""

Else it would return 1

Thanks!

jeffreybrown · Dec 2, 2009

How about...

=IF(AND(L17<=-0.01,ISBLANK(M17),ISBLANK(L18)),"",IF(AND(L17<=-0.01,L18<=-0.0001,ISBLANK(M17)),"",IF(AND(L17<=-0.01,M17<=-0.01,L18<=-0.0001),"",1)))

Blade Hunter · Dec 2, 2009

This should work:

=IF(AND(L17<=-0.01,OR(AND(M17="",L18=""),AND(M17="",L18<=-0.0001),AND(M17<=-0.01,L18<=-0.0001))),"",1)

Bill Rockenbach · Dec 2, 2009

=if(and(l17<=-0.01,or(len(m17&l18)=0,and(l18<=-0.0001,len(m17)=0),and(m17<=-0.01,l18<=-0.0001))),"",1)

samsilverman · Dec 3, 2009

Thanks so much for your ideas, but unfortunately none of these worked. I need a formula that will return "" if any of the scenarios is present. Again, they are.

Scenario 1
L17 has a value
L18 and M17 are both blank
If L17<=-.01, then the formula returns ""
If L17>-.01, then the formula returns 1

Scenario 2
L17 has a value
L18 has a value
M17 is blank
If L17<=-.01 AND L18 <=-.0001 then the formula returns ""
If L17>-.01 OR L18 >-.0001 then the formula returns 1

Scenario 3
L17 has a value
L18 has a value
M17 has a value
If L17<=-.01 AND L18 <=-.0001 AND M17 <=-.01 then the formula returns ""
If L17>-.01 OR L18 >-.0001 OR M17 >-.01 then the formula returns 1

The solutions provided above only returned the right result for scenario 1. I'm thinking the formula would be something like:

IF(OR(scenario1),"",OR(scenario2),"",OR(scenario3),"",1)

But, I don't know how to write it. It's been kicking my butt all day. Thanks for any more help.

Bill Rockenbach · Dec 3, 2009

I tested my formula and it provides a blank for the conditions you specified.

samsilverman · Dec 3, 2009

Just getting going here. Thanks for the reply, Bill. I'll try it again.

samsilverman · Dec 3, 2009

Feel like a Goober!

Finally figured out that L18 was formatted as text. DOH! Thanks all for the solutions.

Bill Rockenbach · Dec 3, 2009

Happens to all of us

Need AND OR Formula Help

samsilverman

Board Regular

Excel Facts

jeffreybrown

Well-known Member

Blade Hunter

Well-known Member

Bill Rockenbach

Well-known Member

samsilverman

Board Regular

Bill Rockenbach

Well-known Member

samsilverman

Board Regular

samsilverman

Board Regular

Bill Rockenbach

Well-known Member

Similar threads

Forum statistics

Share this page

Need AND OR Formula Help

Board Regular

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock