Sorting Increments Data

[hamidun]

Hi all,

I cannot explain my problem by words.

I attached document, in that document I made formula in Pressure cell which is INDEX MATCH MATCH to get the result.

But what I wanted is, when I type 10.4 in Temperature cell, the Pressure cell will show the result which is 1261.24 directly without I have to fill in two temperature cell to make it 10.4.

Thanks for any help!

[AZ-XL]

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))

[Kaper]

Keeping exact match (3rd parameter = 0):

10.4 in Q6 and then:

Please Login or Register  to view this content.

Theoreticaly

Please Login or Register  to view this content.

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

[hamidun]

Wooow, thanks a lot guys!!!! All of your formulas work perfectly...

[Kaper]

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:

Please Login or Register  to view this content.

in S6:

Please Login or Register  to view this content.

in T6:

Please Login or Register  to view this content.

select all 3 and fill down

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

[hamidun]

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))

I guess, there is something wrong with your formula, when i type 6.6 the result shows cell on 6.5

[AZ-XL]

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

[Kaper]

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

[AZ-XL]

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

[hamidun]

@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:

Please Login or Register  to view this content.

in S6:

Please Login or Register  to view this content.

in T6:

Please Login or Register  to view this content.

select all 3 and fill down

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

I marked this thread as solved.

But i am wondering what you are trying to say to me by this post.

Since i used =INDEX(E6:N106, MATCH(ROUNDDOWN(R8,0),D6:D106,0), MATCH(ROUND(MOD(R8,1),1),E5:N5,0)) and it works perfectly.

[Kaper]

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.

[hamidun]

Hi hamidun,

I was refering to:


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.

Yeah, I actually want you to explain a bit the function of "RUNDOWN", "ROUND", "MOD".

Or if you don't mind, would explain a bit how your formula works?