Nesting LEFT into an OR formula

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
232
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Cheers!
I'm trying to incorporate a LEFT function into the following formula so it will look at only the first two digits of CSV!J3 for the numbers 4,5,8,10,11.

=IF(OR(CSV!J3={4,5,8,10,11}),((0.1005*TAN((PI()/180)*(90-(CSV!Z3/2))))-(0.0693*SIN((PI()/180)*(90-CSV!Z3/2))))*2,0)

I tried

=IF(OR(LEFT(CSV!J3={4,5,8,10,11},2)),((0.1005*TAN((PI()/180)*(90-(CSV!Z3/2))))-(0.0693*SIN((PI()/180)*(90-CSV!Z3/2))))*2,0)
but I get a VALUE! error. Can anyone help me fix this?
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
The left function returns a text, now you have to ask for a text
Try:


=IF(OR(LEFT(CSV!J3,2)={"04","05","08","10","11"}),((0.1005*TAN((PI()/180)*(90-(CSV!Z3/2))))-(0.0693*SIN((PI()/180)*(90-CSV!Z3/2))))*2,0)

or

=IF(OR(VALUE(LEFT(CSV!J3,2))={4,5,8,10,11}),((0.1005*TAN((PI()/180)*(90-(CSV!Z3/2))))-(0.0693*SIN((PI()/180)*(90-CSV!Z3/2))))*2,0)
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,975
Office Version
  1. 365
Platform
  1. Windows
deleted

Better solution already provided
 
Last edited:

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
232
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Fantastic! The second suggestion worked best. It recognized entries both with and without the extra fluff after the first two digits.
Thank you!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Try...

=IF(ISNUMBER(MATCH(LEFT(CSV!J3,2)+0),{4,5,8,10,11},0)),((0.1005*TAN((PI()/180)*(90-(CSV!Z3/2))))-(0.0693*SIN((PI()/180)*(90-CSV!Z3/2))))*2,0)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Fantastic! The second suggestion worked best. It recognized entries both with and without the extra fluff after the first two digits.
Thank you!


I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

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
Top