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.
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
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,
Just traded in my OLD subtlety...
for a NUance!
RE: Index/Match function connected to the Ceiling/Floor function
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
COPY the cell.
PASTE SPECIAL VALUES to an empty cell to observe the exact value.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Index/Match function connected to the Ceiling/Floor function
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 value in the lookup table 0.3 or "0.3"?
Skip,
Just traded in my OLD subtlety...
for a NUance!