×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

#N/A error

#N/A error

#N/A error

(OP)
I am using the match function in cell k5 and k6. Every so often the cell k5 shows #N/A when I paste data into the highlighted cells. Can someone tell me what I can do to correct this error from happening? I have attached the spread sheet.

RE: #N/A error

I would try two things.  First, I would change the starting cell of the match array from F1 to F2 since F1 is a header.  Secondly, I would try using the numeric value of 0.00 instead of the text value "No shear".

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

RE: #N/A error

And remove the zero as the match type.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

RE: #N/A error

(OP)
I Tried all of the above but didn't do it. I need the zero in the match type as I am looking up the exact #. Any other suggestions?

RE: #N/A error

Make sure that the precision of the lookup value matches the precision of the lookup table.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

RE: #N/A error

K5 is trying to match J5 to the array.  With the data you have in there, 22.63 is the max shear you have. However, this value is based on an absolute value of your shear, and in the array the "max value" is negative.

Use:

=IF(ISERROR(MATCH(J5,F1:F1061,0)),MATCH(-J5,F1:F1061,0),MATCH(J5,F1:F1061,0))

RE: #N/A error

Replace "no shear" in the formula in column F with 0, then put =ABS(...) around the whole formula.  

You are using an absolute value as your lookup value, so the numbers in your lookup column must be absolute as well.

 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: #N/A error

As TDAA and IDS say you are trying to match a negative number to a positive one.
You can also get problems if one is formatted as text and the other is a number.

RE: #N/A error

The text "No Shear" within the column has no effect.  The numbers will not be considered text since they are a calculated value. The formula I put up will give the right row for a positive or negative result.

One thing to note, you have multiple values in the sheet that would be the maximum (at least as displayed for the 22.63), so you may have a different row with the same maximum.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources