if statement referencing a nested if(mid) statement

**kantjustice** · 04-14-2016, 05:49 PM

I am using an if statement that references an nested if/mid statement on another sheet and it is returning all false when one is true.

Cell data
Strike Last Net Bid Ask Vol Int Strike
FXY1615D65-E* 0 0 23.3 24.1 0 0 65

FXY1615D72.5-E* 0 0 15.9 16.3 0 0 72.5

FXY1615D105-E* 0 0 0 0.15 0 0 105

=IF(MID(C11,11,1)=".",MID(C11,9,4),IF((MID(C11,12,1)="-"),MID(C11,9,3),MID(C11,9,2)))

The above nested function first references first "strike" and then returns the numbers to second "strike." The function works properly. However, when I do an if inequality referencing on another sheet, one of the outputs, such as 105, it returns false even when it is true.

**TMS** · 04-14-2016, 05:55 PM

MID is a Text function and produce a text string, that is, it will return text "105", not the number 105. Hence they are not equal.

Try:

Formula:

Please Login or Register  to view this content.

**kantjustice** · 04-14-2016, 06:02 PM

Brilliant! The text nature of Mid did not dawn on me. Worked like a charm.

**TMS** · 04-14-2016, 06:27 PM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

if statement referencing a nested if(mid) statement

LinkBack

Thread Tools

Rate This Thread

Display

if statement referencing a nested if(mid) statement

Re: if statement referencing a nested if(mid) statement

Re: if statement referencing a nested if(mid) statement

Re: if statement referencing a nested if(mid) statement

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Nested IF Statement with OR Clause Only Processes First IF Statement

nested IF statement or AND? trying to write an IF statement with multiple conditions

[SOLVED] If statement to select data - nested statement - assistance

[SOLVED] Nested If Statement - ISBLANK and ISNA in same statement?

If statement Referencing another IF statement

[SOLVED] Nested IF Statement with AND Statement

Nested IF Statement-How can I write a Nested IF statement

Bookmarks

Bookmarks

Posting Permissions