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!

*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.

Jobs

Index/Match function connected to the Ceiling/Floor function

Index/Match function connected to the Ceiling/Floor function

(OP)
Hi there!

I am having an issue with the Index/Match function connected to the Ceiling/Floor function - I was wondering if you could provide some guidance.

I could email you the SpreadSheet or I can try and explain it.

Here is the simple explanation;

(1) For these values, everything work.

A24 = 0.14 ------##(input)
A23 = FLOOR(A23,0.1) = 0.10
A25 = CEILING(A23,0.1) = 0.20

B23 = INDEX(ARRAY,MATCH(A22,ARRAY,0)) = 0.6
B25 = INDEX(ARRAY,MATCH(A24,ARRAY,0)) = 0.7
B24 = INTERPOLATION BETWEEN B22&B24 WITH RESPECT TO A24 = 0.64

(2) when A24 is a number <0.3999 & >0.30001 - I get an error.

A24 = 0.38 (or any other number >0.30001 and <0.3999) ------##(input)
A23 = FLOOR(A23,0.1) = 0.30
A25 = CEILING(A23,0.1) = 0.40

B23 = INDEX(ARRAY,MATCH(A22,ARRAY,0)) = #N/A
B25 = INDEX(ARRAY,MATCH(A24,ARRAY,0)) = 0.8
B24 = INTERPOLATION BETWEEN B22&B24 WITH RESPECT TO A24 = #N/A

B23 gives me an error (#N/A) only when 0.3001<A23<0.3999 - This happens when "A23" is formulated with the FLOOR function.

However, if I manually change "A23" to 0.3 - then "B23" yields a valid result.
Keep in mind that this only happens when 0.3001<A23<0.3999 - At any other range, the functions work just fine.

Please let me know if you have any insight on this.
I think it might be a bug but I am not quite sure.

Thank you.

RE: Index/Match function connected to the Ceiling/Floor function

Hi,

The 0 in your MATCH() function means that it wants an EXACT match. You need a 1 or -1 depending how your lookup data is sorted.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Index/Match function connected to the Ceiling/Floor function

(OP)
Skip,
Actually I want excel to give me the EXACT match.

Please, if you don't mind, refer to the attached image for the following explanation;
"E17" = 0.3
"F17" = 0.7
The values from "F15" to "F19" are the data values in the array.

"A24" = 0.38
"A23" = FLOOR(A24,0.1) = 0.3
"A25" = CEILING(A24,0.1) = 0.4

"B23" = INDEX (F15&F19 , MATCH (A23 , E15&E19 , 0) )

If I have this way, excel should give me the EXACT match which in this case would be 0.70 - not #N/A

Thanks!

RE: Index/Match function connected to the Ceiling/Floor function

Is the value EXACTLY 0.3?

COPY the cell.

PASTE SPECIAL VALUES to an empty cell to observe the exact value.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Index/Match function connected to the Ceiling/Floor function

(OP)
Skip,

Thank you very much for your help - I did what you suggested and it worked!
I assigned a (-1) value instead of (0) and it was able to extract the accurate number.

I also did what you suggested with the COPY / PASTE SPECIAL -
the values that was "pasted" was a "0.3" - which is what was expected.
I am still not sure why it wouldn't work with EXACT MATCH.

It could be a bug. Like I said before, with any other number rather than "0.3", the functions were perfectly.

Thanks a lot!

RE: Index/Match function connected to the Ceiling/Floor function

Is the lookup value 0.3 or "0.3"?

Is the value in the lookup table 0.3 or "0.3"?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close