Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel: Formula only works with certain numerical values

PSchubert

PSchubert

Technical User
Jun 6, 2006
57
AU
Hello Mighty Excelers!

I'm stumped. I have formulae that extract numbers from imperial linear measurements which are formatted in specific ways, for example: 12' 11-13/16" or 11-13/16" (when there are no feet) or 11" (when there are no feet and no fraction) or 13/16" (when there are no feet and no whole inches).

The linear measurement string is in I7; the whole inches extract to M8; the numerator extracts to M9, and the denominator extracts to M10.

The formula in M8 works as it should when I7 is feet' inches-numerator/denominator" or inches-numerator/denominator" or inches" or 0-numerator/denominator".

If I don't specify 0- for 0 inches, here's the problem: when I enter into I7 13/16" or 14/16" or 15/16" (without whole inches), the formula in M8 works perfectly, i.e., returns 0 whole inches. If the denominator is 16 and the numerator is greater than 12, (for example, 50/16"), the formula works as intended. When I enter any other fraction into I7 (without whole inches), in M8 I get values in the 40000s, when it should still return 0 whole inches. The correct numerator and denominator are always returned, so the error must be in the M8 formula.

Here it is, ugly as it may be: =IF(I7="",0,IFERROR((MID(I7,SEARCH(" ",I7)+1,SEARCH("-",I7)-SEARCH(" ",I7)-1)*1),IFERROR((ABS(LEFT(I7,SEARCH("-",I7)-1))),IFERROR((ABS(LEFT(I7,SEARCH(CHAR(34),I7)-1))),IFERROR((IF(AND(ISERROR(SEARCH("-",I7)),ISNUMBER(SEARCH("/",I7))),0)),0)))))

I've stretched it out to make it easier to isolate here, with comments in red and square brackets:

=IF(I7="",0,

IFERROR(

(MID(I7,SEARCH(" ",I7)+1,SEARCH("-",I7)-SEARCH(" ",I7)-1)*1), [return the value between a space and a hyphen {if there are feet}]

IFERROR(

(ABS(LEFT(I7,SEARCH("-",I7)-1))), [return the value to the left of a hyphen {if there are no feet}]

IFERROR(

(ABS(LEFT(I7,SEARCH(CHAR(34),I7)-1))), [return the value to the left of a quote {if there are no feet and no fraction}]

IFERROR(

(IF(AND(ISERROR(SEARCH("-",I7)),ISNUMBER(SEARCH("/",I7))),0) [return 0 if there is no hyphen but there is a slash {if there are no feet and no whole inches}]
[I think the above line is where the error happens, or at least this line doesn't fix the error]

),0)))))

I've attached a zipped file of what I have so far. I had to zip it, as attaching an xlsx is apparently not supported.

If any of you kind gurus can please help me, I'd bestow upon you my fondest wishes!

Thank you so much,

Phillip
 

Attachments

  • FixMe.zip
    7.6 KB · Views: 1
Last edited:
Sort by date Sort by votes
Have you considered creating a VBA function that returns the appropriate values. You might even find one on line. Your massive expressions seem unmanageable to me.
 
Upvote 0 Downvote
dhookom said:
Have you considered creating a VBA function that returns the appropriate values. You might even find one on line. Your massive expressions seem unmanageable to me.
Click to expand...
Hello dhookum,

Thanks for your reply. I have considered it, but would rather not have an xlsm, as I need it to be as user-friendly as possible for unskilled workmates to open on their work machines and phones without having to enable macros and make it a trusted document.

Phillip
 
Last edited:
Upvote 0 Downvote
dhookem is correct. This formula is just too much for Excel.

That said, if you must, I think what you should do is break your formula down.

Take each "unit" of the formula and put it in it's own cell so you can see the value that is being returned. In this way you can "debug" the formula to see where the problem is. Once you have all the "units" of the formula working as expected, you can re-assemble the entire formula and have confidence it is working as expected.

To begin the process, here's a "view" of your formula (but don't assume I've done this correctly):
Code: 
=IF(I7="",0,
    IFERROR(
        (
            MID(I7,SEARCH(" ",I7)+1,SEARCH("-",I7)-SEARCH(" ",I7)-1) * 1
        ),
        IFERROR(
            (
                ABS(LEFT(I7,SEARCH("-",I7)-1))
            ),
            IFERROR(
                (ABS(LEFT(I7,SEARCH(CHAR(34),I7)-1))),
                IFERROR(
                    (IF(
                        AND(
                            ISERROR(SEARCH("-",I7)),
                            ISNUMBER(SEARCH("/",I7))
                        ),
                        0
                    )),
                0)
            )
        )
    )
)

To break it down you will need to, for example, turn
Code: 
MID(I7,SEARCH(" ",I7)+1,SEARCH("-",I7)-SEARCH(" ",I7)-1) * 1
into cells that show what
Code: 
SEARCH(" ",I7)
returns.

And in another cell show what
Code: 
SEARCH("-",I7)
returns.

Then, start assembling the units to see what they return.
And so on and so on...

It's a lot of work but as I see it, that's your only option.
 
Upvote 0 Downvote
The approach BF...KC suggests is almost certainly what you will need to do with a formula like the one you are wrestling with. However if you have some suspicions of where your problem lies you can evaluate internal parts of the entire formula by highlighting it in the editing bar and pressing F9. Then you can un-F9 the formula with the Escape key (provided you do this immediately).
 
Upvote 0 Downvote
BFroeschlKC said:
dhookem is correct. This formula is just too much for Excel.

That said, if you must, I think what you should do is break your formula down.

Take each "unit" of the formula and put it in it's own cell so you can see the value that is being returned. In this way you can "debug" the formula to see where the problem is. Once you have all the "units" of the formula working as expected, you can re-assemble the entire formula and have confidence it is working as expected.

To begin the process, here's a "view" of your formula (but don't assume I've done this correctly):
Code: 
=IF(I7="",0,
    IFERROR(
        (
            MID(I7,SEARCH(" ",I7)+1,SEARCH("-",I7)-SEARCH(" ",I7)-1) * 1
        ),
        IFERROR(
            (
                ABS(LEFT(I7,SEARCH("-",I7)-1))
            ),
            IFERROR(
                (ABS(LEFT(I7,SEARCH(CHAR(34),I7)-1))),
                IFERROR(
                    (IF(
                        AND(
                            ISERROR(SEARCH("-",I7)),
                            ISNUMBER(SEARCH("/",I7))
                        ),
                        0
                    )),
                0)
            )
        )
    )
)

To break it down you will need to, for example, turn
Code: 
MID(I7,SEARCH(" ",I7)+1,SEARCH("-",I7)-SEARCH(" ",I7)-1) * 1
into cells that show what
Code: 
SEARCH(" ",I7)
returns.

And in another cell show what
Code: 
SEARCH("-",I7)
returns.

Then, start assembling the units to see what they return.
And so on and so on...

It's a lot of work but as I see it, that's your only option.
Click to expand...
Thank you for your reply, BFroeschlKC. I will use your method to see if I can isolate the problem. I'll post back to relay the results.
 
Upvote 0 Downvote
Deniall said:
The approach BF...KC suggests is almost certainly what you will need to do with a formula like the one you are wrestling with. However if you have some suspicions of where your problem lies you can evaluate internal parts of the entire formula by highlighting it in the editing bar and pressing F9. Then you can un-F9 the formula with the Escape key (provided you do this immediately).
Click to expand...
Thank you for your reply, Deniall. I'll take your advice and see if that method helps me to solve the issue.
 
Upvote 0 Downvote
UPDATE: I've isolated the issue to this line:

(ABS(LEFT(I7,SEARCH(CHAR(34),I7)-1)))

When put into its own cell, it needs to be =IFERROR((ABS(LEFT(I7,SEARCH(CHAR(34),I7)-1))),0). Instead of returning 0 when the content in I7 is only any fraction of an inch except numerator > 12/denominator = 16", it returns a number in the 40000s (13/16" returns 0, 32/16" returns 0, etc., but 11/16" returns 42675).

Now I need to work on the tweak. I would still love if a bigger brain than mine has a reason why and a way forward.
 
Upvote 0 Downvote
UPDATE #2

I thought I solved it by removing the -1, but there's a problem elsewhere with it now. AAARGH. Still working to fix it.
 
Upvote 0 Downvote
Take a look at Doug's collection.

newtonexcelbach.com

Feet and inches, and other units

Following a query at Eng-tips I have had a look at options in Excel for converting metres to feet and inches, with the inches displayed as a fraction rather than a decimal. Converting from metres t…
newtonexcelbach.com newtonexcelbach.com
 
Upvote 0 Downvote
Yet another reason (out of 1000's) to join XIX century (at least) and move to Metric system, like the rest of the world...
🌏
 
Upvote 0 Downvote
Andrzejek said:
Yet another reason (out of 1000's) to join XIX century (at least) and move to Metric system, like the rest of the world...
🌏
Click to expand...
Exactly! This post relates to a larger project of converting imperial linear measurements to metric.
 
Upvote 0 Downvote
Give users an xlsm with a small subscript that sets trusted locations.
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

arvarr
  • Locked
  • Question
Excel Formula Help 1
Replies
7
Views
387
SkipVought
S
Andrzejek
  • Question
Formula with & and a single quote 2
Replies
4
Views
507
OzFoxy
OzFoxy
renigar
  • Question
Having trouble with Excel Lookup
Replies
5
Views
435
renigar
renigar
remeng
  • Locked
  • Question
Excel Pivot Table - Combine Values into a single row
Replies
16
Views
1K
remeng
remeng
TomSalvato
  • Locked
  • Question
EXCEL-formula help 2
Replies
6
Views
237
TomSalvato
TomSalvato

Part and Inventory Search

Sponsor

Back
Top