Need Iteration formula by index and match function

[Rais Ahmed]

Hi Every one,

I had one very good and almost accurate excel formula for calculating a specific value by index and match function. I lost my external hard drive and that formula too. Can body body help me to do that formula again as I almost not working on excel over two year and above. What I need to calculate value between to 2 number based on another value. As you can see below H is Height and A is some value relative to specific value in column H. If I want to calculate value of A based on H = 4 what should be formula. Thank you for your time reading this.
[TABLE="width: 128"]
<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: center"]H
[/TD]
[TD="class: xl65, width: 64, align: center"]A
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]3
[/TD]
[TD="class: xl65, align: center"]0.83
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]5[/TD]
[TD="class: xl65, align: center"]0.88
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]10[/TD]
[TD="class: xl65, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]15
[/TD]
[TD="class: xl65, align: center"]1.03[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]20[/TD]
[TD="class: xl65, align: center"]1.06[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]30[/TD]
[TD="class: xl65, align: center"]1.09
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]40[/TD]
[TD="class: xl65, align: center"]1.12[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]50[/TD]
[TD="class: xl65, align: center"]1.14[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]60[/TD]
[TD="class: xl65, align: center"]1.15[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]80[/TD]
[TD="class: xl65, align: center"]1.18[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]100[/TD]
[TD="class: xl65, align: center"]1.2
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]120[/TD]
[TD="class: xl65, align: center"]1.22[/TD]
[/TR]
</tbody>[/TABLE]

 

[palaeontology]

Probably not much use to you, but your figures reasonably closely fit the following natural logarithmic curve ...

= 0.103*LN(A2) + 0.7352

type this into a relevant cell and copy down ... the current formula uses A2 assuming the 'H' value of 3 is in cell A2.

I'm not sure what you were meaning when you said you used to use Index/Match.

Kind regards,

Chris

 

[Rais Ahmed]

Dear Chris,

First of all Thank you very much for reply and try to understand my complex formula. Your formula will not work as I need iteration formula which I had before using index and match function. Now I will explain you in details, the values in Column H and A is predetermined values which we cannot change. I need value between the two values within range value from column H. Suppose I need to find value of 12 what should be value from column A,? within range value of 10 and 15 from column H.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]12
[/TD]
[TD]Value from below range = ?
[/TD]
[/TR]
[TR]
[TD]H
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0.83
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]0.88
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]1.03
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]1.06
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]1.09
[/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD]1.12
[/TD]
[/TR]
[TR]
[TD]50
[/TD]
[TD]1.14
[/TD]
[/TR]
[TR]
[TD]60
[/TD]
[TD]1.15
[/TD]
[/TR]
[TR]
[TD]80
[/TD]
[TD]1.18
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]1.2
[/TD]
[/TR]
[TR]
[TD]120
[/TD]
[TD]1.22
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
Rais

Probably not much use to you, but your figures reasonably closely fit the following natural logarithmic curve ...

= 0.103*LN(A2) + 0.7352

type this into a relevant cell and copy down ... the current formula uses A2 assuming the 'H' value of 3 is in cell A2.

I'm not sure what you were meaning when you said you used to use Index/Match.

Kind regards,

Chris

 

[Aladin Akyurek]

So you don't know yourself what A will be when H = 4. If so, this is not an Excel formula question.

 

[Rais Ahmed]

So you don't know yourself what A will be when H = 4. If so, this is not an Excel formula question.

A will be value between 0.83 to 0.88. I can simply take this value and divided by 2 to get value for 4. But as you go down interval between two values is very large and this divided by 2 method will not work to get exact value.

Regards,
Rais

 

[barry houdini]

Are you expecting linear interpolation? For example if lookup value is 12 that 2/5ths of the way between 10 and 15 so result should be 2/5ths of the distance between 1 and 1.03, i.e. 1.012

For that result with "H values" in A3:A14 and "A values" in B3:B14 try this formula

=LOOKUP(A1,A3:A13,B3:B13+(B4:B14-B3:B13)*(A1-A3:A13)/(A4:A14-A3:A13))

 

[palaeontology]

Ok,

this formula should work for what I think you're after ...

=OFFSET(A1,MATCH(D2,A2:A13,1),1)+((D2-OFFSET(A1,MATCH(D2,A2:A13,1),0))/(OFFSET(A1,MATCH(D2,A2:A13,1)+1,0)-OFFSET(A1,MATCH(D2,A2:A13,1),0))*(OFFSET(A1,MATCH(D2,A2:A13,1)+1,1)-OFFSET(A1,MATCH(D2,A2:A13,1),1)))

this assumes ...

* your table exists in range A2:A13 ... adjust formula as needed
* the H value to be researched is in D2 ... adjust formula as needed

this formula returns an A value of 0.877988 when H is 4 for example

the way it works is ... I'll use an H value of 12 as an example ...

* it finds that 12 exists between the provided H values of 10 and 15
* it finds that 12 is two-fifths of the way through that range of 10 and 15
* it finds the corresponding A values (for H values of 10 and 15) are 1 and 1.03
* it finds the value that is two-fifths of the way through that range of 1 and 1.03 .... this equates to 0.012
* it then adds the 0.012 to the lower of the two A values in the range investigates ... in this case 1

so the final answer is that when H = 12, A = 1.012

I think this is what you're asking for

Kind regards,

Chris

 

[palaeontology]

wow, just saw that barry had responded to your job while i was entering mine

looks like we're doing the same thing, buy barry's is far shorter and therefore more elegant than mine

Kind regards,

Chris

 

[Rais Ahmed]

Hi Barry & Chris,

Mr. Barry, Your formula work perfect man!!!!!!!!!!!! It gives exact values. I am really appreciating your effort to help me, this is what exactly I want. Thank you very much guys.

Mr. Chris, I didn't check your formula yet and taken your advise to check Mr. Barry's one. However I will check it later on and let you know if it will work also.

Thank you very much both of you!!!!!!!!!!

Regards,
Rais

 

[Rais Ahmed]

Hi Chris,

I checked your formula it work perfectly fine like Mr. Barry's formula. Thanks for your support and valuable time!!!
The only difference is that as you mentioned below (this formula returns an A value of 0.877988 when H is 4 for example) returned 0.8550 as in my case correct. However formula work for me great and thanks again!

Regards,
Rais

Ok,

this formula should work for what I think you're after ...

=OFFSET(A1,MATCH(D2,A2:A13,1),1)+((D2-OFFSET(A1,MATCH(D2,A2:A13,1),0))/(OFFSET(A1,MATCH(D2,A2:A13,1)+1,0)-OFFSET(A1,MATCH(D2,A2:A13,1),0))*(OFFSET(A1,MATCH(D2,A2:A13,1)+1,1)-OFFSET(A1,MATCH(D2,A2:A13,1),1)))

this assumes ...

* your table exists in range A2:A13 ... adjust formula as needed
* the H value to be researched is in D2 ... adjust formula as needed

this formula returns an A value of 0.877988 when H is 4 for example

the way it works is ... I'll use an H value of 12 as an example ...

* it finds that 12 exists between the provided H values of 10 and 15
* it finds that 12 is two-fifths of the way through that range of 10 and 15
* it finds the corresponding A values (for H values of 10 and 15) are 1 and 1.03
* it finds the value that is two-fifths of the way through that range of 1 and 1.03 .... this equates to 0.012
* it then adds the 0.012 to the lower of the two A values in the range investigates ... in this case 1

so the final answer is that when H = 12, A = 1.012

I think this is what you're asking for

Kind regards,

Chris