Please, help. Look picture. It`s a challenging task[ATTACH=CONFIG]62852[/ATTACH]
http://i58.tinypic.com/2d15qap.jpg
Match, Floor, Hlookup
-
-
-
Re: Match, Floor, Hlookup
B.W.B.
Welcome to the forum..
if the value is in D13 then in E13 [bfn]=INDEX(C2:L7,MATCH(D13,B2:B7,1),MATCH(MOD(D13,10),C1:L1,0))[/bfn] -
Re: Match, Floor, Hlookup
i dont understand. Value is: 23.6inches Values are B2:B7 and C1:L1 and result is in table(gallons)! Is that formula giving me 236gallons??
-
Re: Match, Floor, Hlookup
hmm. hard to follow but i think you want
[bfn]=INDEX(C2:L7,MATCH(TRUNC(D13),B2:B7,1),MATCH(MOD(TRUNC(D13),10),C1:L1,0))+(D13-TRUNC(D13))*10[/bfn]why not just times the 23.6 by ten
23.6 *10 =236 -
Re: Match, Floor, Hlookup
Quote from pike;730012hmm. hard to follow but i think you want
[bfn]=INDEX(C2:L7,MATCH(TRUNC(D13),B2:B7,1),MATCH(MOD(TRUNC(D13),10),C1:L1,0))+(D13-TRUNC(D13))*10[/bfn]why not just times the 23.6 by ten
23.6 *10 =236No. That table is just example! Real table is all randome numbers... It is 'simple': higher inch MINUS lower TIMES or multipy (English&math i'm not so good) by 10 PLUS lower inch IS RESULT. It's simple.
-
-
Re: Match, Floor, Hlookup
for 23.6 this formula witll select the 20 from the column and three from the row =230
INDEX(C2:L7,MATCH(TRUNC(D13),B2:B7,1),MATCH(MOD(TRUNC(D13),10),C1:L1,0))
and to add the decimal as a whole hnmber just add +(D13-TRUNC(D13))*10 to the end of the formula
-
Re: Match, Floor, Hlookup
Quote from pike;730014for 23.6 this formula witll select the 20 from the column and three from the row =230
INDEX(C2:L7,MATCH(TRUNC(D13),B2:B7,1),MATCH(MOD(TRUNC(D13),10),C1:L1,0))
and to add the decimal as a whole hnmber just add +(D13-TRUNC(D13))*10 to the end of the formula
Let's try, shall we?
Here is the real thing: so i'm from europe's middle of nowhere::
pdf to xlsx not good job! Let's start: example value is: 23,6cm THAT IS 2232 MINUS 2086 TIMES 0,6 or MULTIPY by 10 i think, PLUS 2086 IS=2174 litres. Hope you understand this 50 000 litres fuel reservoire:)
I almost forgott, INDEX may not work because, value sheet and table are on different sheets!
-
Re: Match, Floor, Hlookup
I think I see what you are trying to do. You are trying to linearly interpolate 23.6 between the values for 23 and 24. Therefore, the difference of the value for 24-23 is multiplied by 0.6 and added to the value of 23 to find the answer. Since the difference 2232-2086 = 146, and since 146*0.6 is 87.6, this number is rounded up to 88 and added to 2086 to get the value of 2174.
I don't have time to find the formula now, but this may help others.
-
Re: Match, Floor, Hlookup
Will any decimal values be entered as, for example, '23.6' or '23point6', as in your original example? If it's '23point6', this will need to be converted to a number before it can be used (possibly several times); could we use a separate 'helper' cell to do that to avoid unnecessary complexity within the formula?
-
Re: Match, Floor, Hlookup
Subject to my previous question, try:
=INDEX(Reservoir,MATCH(FLOOR(C15,10),RowHdrs,0),MATCH(FLOOR(C15,1)-FLOOR(C15,10),ColHdrs,0))+(IF(CEILING(C15,10)-CEILING(C15,1)=0,INDEX(Reservoir,MATCH(CEILING(C15,10),RowHdrs,0),1),INDEX(Reservoir,MATCH(FLOOR(C15,10),RowHdrs,0),MATCH(CEILING(C15,1)-FLOOR(C15,10),ColHdrs,0)))-INDEX(Reservoir,MATCH(FLOOR(C15,10),RowHdrs,0),MATCH(FLOOR(C15,1)-FLOOR(C15,10),ColHdrs,0)))*MOD(C15,1)
It assumes that your data, excluding headers, is in a range called 'Reservoir', and that the row and column headers are in ranges 'RowHdrs' and 'ColHdrs'. That will get round the complexity of the data being in different worksheets.
-
-
Re: Match, Floor, Hlookup
I'm in Europa, so 23ponit6 is yours(usa) 23.6, otherwise is 23rd june
-
Re: Match, Floor, Hlookup
Thank you, Pike. This is it. Thank you all, to forum. God bless us all!:ole:
-
Re: Match, Floor, Hlookup
Quote from Batman;730028Subject to my previous question, try:
=INDEX(Reservoir,MATCH(FLOOR(C15,10),RowHdrs,0),MATCH(FLOOR(C15,1)-FLOOR(C15,10),ColHdrs,0))+(IF(CEILING(C15,10)-CEILING(C15,1)=0,INDEX(Reservoir,MATCH(CEILING(C15,10),RowHdrs,0),1),INDEX(Reservoir,MATCH(FLOOR(C15,10),RowHdrs,0),MATCH(CEILING(C15,1)-FLOOR(C15,10),ColHdrs,0)))-INDEX(Reservoir,MATCH(FLOOR(C15,10),RowHdrs,0),MATCH(FLOOR(C15,1)-FLOOR(C15,10),ColHdrs,0)))*MOD(C15,1)
It assumes that your data, excluding headers, is in a range called 'Reservoir', and that the row and column headers are in ranges 'RowHdrs' and 'ColHdrs'. That will get round the complexity of the data being in different worksheets.
Too many arguments:(
-
Re: Match, Floor, Hlookup
Batman, Pike, pardon moi, thank you all. Finito. This is it:
=(INDEX('S-7'!B2:K27;MATCH(TRUNC(Sheet1!C2)+1;'S-7'!A2:A27;0);MATCH(MOD(TRUNC(Sheet1!C2)+1;10);'S-7'!B1:K1;0))-INDEX('S-7'!B2:K27;MATCH(TRUNC(Sheet1!C2);'S-7'!A2:A27;1);MATCH(MOD(TRUNC(Sheet1!C2);10);'S-7'!B1:K1;0)))*(Sheet1!C2-TRUNC(Sheet1!C2))+INDEX('S-7'!B2:K27;MATCH(TRUNC(Sheet1!C2);'S-7'!A2:A27;1);MATCH(MOD(TRUNC(Sheet1!C2);10);'S-7'!B1:K1;0))S-7 is reservoire table!
A2:A27 rows
B1:K1 colums
Sheet1 C2 is input value in centimetres and milimetres.
Result cell is also on sheet1Thank you all, from Croat, from Herzegovina!
In formula where is ) for english countries is (,)coma
-
Re: Match, Floor, Hlookup
:ole:
-
-
Re: Match, Floor, Hlookup
So this is previous formula: =ROUND(IF(AND(B5>=0,B5<241.1),(INDEX('S-7'!B15:K41,MATCH(TRUNC(Sheet1!B5)+1,'S-7'!A15:A42,1),MATCH(MOD(TRUNC(Sheet1!B5)+1,10),'S-7'!B14:K14,0))-INDEX('S-7'!B15:K41,MATCH(TRUNC(Sheet1!B5),'S-7'!A15:A42,1),MATCH(MOD(TRUNC(Sheet1!B5),10),'S-7'!B14:K14,0)))*(Sheet1!B5-TRUNC(Sheet1!B5))+INDEX('S-7'!B15:K41,MATCH(TRUNC(Sheet1!B5),'S-7'!A15:A42,1),MATCH(MOD(TRUNC(Sheet1!B5),10),'S-7'!B14:K14,0)),"ERROR"),0)
So, khmm, khmm, mister Batman and mister Pike: Please, now i need to find height in reservoire for, let's say, 33 333Litres??? Please, help, or give guidelines.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!