Sorting Increments Data

Hi
You should enter desired input to q6 cell (10.4 ; 9.56).
=INDEX(D5:M15,MATCH(Q6,C5:C15,1),MATCH(Q6-TRUNC(Q6),D4:M4,1))

Keeping exact match (3rd parameter = 0):

10.4 in Q6 and then:

=INDEX(C3:M15, MATCH(ROUNDDOWN(Q6,0),C3:C15,0), MATCH(ROUND(MOD(Q6,1),1),C4:M4,0))

Theoreticaly

=INDEX(C3:M15, MATCH(ROUNDDOWN(Q6,0),C3:C15,0), MATCH(MOD(Q6,1),C4:M4,0))

shall be enough, but because of problems with accuracy of floating point calculations rounding is necessary in this case.

Not really all ;-).

@AZ-XL - note my comment on accuracy - it applies also to your case.
Check for instance values for 2.3 or 2.4 etc.

@hamidun: check values in the table - you have non-numbers there like 2.3 - 72l.228 instead of 721.228

as for checking - write 0 in Q6 0.1 in Q7
select both and fill down until 10.9
then in R6:

=INDEX($C$3:$M$15, MATCH(ROUNDDOWN(Q6,0),$C$3:$C$15,0), MATCH(ROUND(MOD(Q6,1),1),$C$4:$M$4,0))

in S6:

=INDEX($D$5:$M$15,MATCH(Q6,$C$5:$C$15,1),MATCH(Q6-TRUNC(Q6),$D$4:$M$4,1))

in T6:

=R6=S6

select all 3 and fill down

note FALSE values in somme cases and compare values for such rows with the table.

Hi
I do not know why formula behaves like that.
Actually result have to be as supposed. Try this one
=INDEX(D5:M15,MATCH(Q6,C5:C15,1),(Q6-TRUNC(Q6))*10+1)

Or you can use Kaper"s formula

Hi, not really "have to".
If excel would use numbers as we were taught in school on maths course - yes, have to.
But Excel uses floating point representation of the number.
Simple test:
Try writing 12345678901234567890 in one cell.
Excel will first show it as 1.23457E+19.
But have a look in formula bar. What you see?
12345678901234500000 only, so we are 67890 short!
What inaccuracy!

My other favourite one:
=SIN(RADIANS(360))
What? Shall be 0!


More detailed explanation here: http://support.microsoft.com/kb/78113

Thank you Kaper for this information. Once I met this kind of problem but there was decimal numbers until 6-8 level (0.12345678....). Havent thought that I will meet this problem again.

Thank you again

Hi hamidun,

I was refering to:

All of your formulas work perfectly

And also tried to obtain some "didactic" effect. I hope you not only use the ready formulas, but try to learn how they work, to write similar by yourself next time you have a similar need.