Please Help

**leo73pk** · 10-04-2006, 09:02 PM

DEAR READERS,

Kindly help to resolve the following problem.

Dear Readers,

I need your assistance to resolve the mentioned below problem.

FILE-1
In cell A2 we have height (in meters) and in B2 we want equivalent ltrs.

FILE-2
In column A we have meters & in column b we have equivalent litters but values appears like this

A B
1 Height Litres
2 5.250 5246640
3 5.260 5256799
4 5.270 5266958
5 5.280 5277117

PROBLEM

1) If we have value 5.250 in Col A2 of File-1 it will return the value from File-2 by looking up 5.250 in Column A of File-2 & return value from Column B. (which can be done by simple vlookup.)

2) What would be the formula in Cell B2 of File-1 to calculate the Ltrs if value in Cell A2 of File-1 is 5.254.

Methodology
As this figure 5.254 lies between 5.250 & 5.260 for which equivalent ltrs are given in File-2. therefore we need to do following calculations to get the litres at height 5.254

Take 5.250 & 5.260 as 5.254 lies between these two heights & take equivalent ltrs from column-b of file-2.

The Logical Formula which Calculate the liters at height 5.254 is as follows (from File-2)

=(5256799-5246640)/(5.260-5.250)*(5.254-5.250)+5246640

= 5,250,704

Required solution:

Formula in cell b2 of file-1 which perform following tasks;

Check the value of Cell A2 of File-1 in File-2 Column A & if value available in Column A of File-2 return equivalent liters from Column B of File-2, but if the exact value of Cell A2 of File-1 not found in Column A of File-2 calculate the value as per above methodology.

Regards

jbman

**Bryan Hessey** · 10-04-2006, 09:31 PM

Originally Posted by leo73pk

DEAR READERS,

Kindly help to resolve the following problem.

Dear Readers,

I need your assistance to resolve the mentioned below problem.

FILE-1
In cell A2 we have height (in meters) and in B2 we want equivalent ltrs.

FILE-2
In column A we have meters & in column b we have equivalent litters but values appears like this

A B
1 Height Litres
2 5.250 5246640
3 5.260 5256799
4 5.270 5266958
5 5.280 5277117

PROBLEM

1) If we have value 5.250 in Col A2 of File-1 it will return the value from File-2 by looking up 5.250 in Column A of File-2 & return value from Column B. (which can be done by simple vlookup.)

2) What would be the formula in Cell B2 of File-1 to calculate the Ltrs if value in Cell A2 of File-1 is 5.254.

Methodology
As this figure 5.254 lies between 5.250 & 5.260 for which equivalent ltrs are given in File-2. therefore we need to do following calculations to get the litres at height 5.254

Take 5.250 & 5.260 as 5.254 lies between these two heights & take equivalent ltrs from column-b of file-2.

The Logical Formula which Calculate the liters at height 5.254 is as follows (from File-2)

=(5256799-5246640)/(5.260-5.250)*(5.254-5.250)+5246640

= 5,250,704

Required solution:

Formula in cell b2 of file-1 which perform following tasks;

Check the value of Cell A2 of File-1 in File-2 Column A & if value available in Column A of File-2 return equivalent liters from Column B of File-2, but if the exact value of Cell A2 of File-1 not found in Column A of File-2 calculate the value as per above methodology.

Regards

jbman

Was the solution to this homework question not already supplied in H1.xls attachment to Post 12 of your 'Lookup Function Help Required-Urgent' post?

Is there further information required? - if so please ask that further information rather than to re-state the full question

---

**leo73pk** · 10-05-2006, 07:01 AM

Dear Brayn,

The solution you proposed in H1.xls is not the desired solution. I think you did'nt fully understood the scenario.

Regard

Jbman.

**Bryan Hessey** · 10-05-2006, 08:12 AM

Originally Posted by leo73pk

Dear Brayn,

The solution you proposed in H1.xls is not the desired solution. I think you did'nt fully understood the scenario.

Regard

Jbman.

Or perhaps I did. You are asking for help to calculate the increase that needs to occur where a 'VLookup ,true' returns a value that is less than the one you sought.

You have been provided with the means to determine the Value basis on which the value was returned (VLookup, ,1,true) and to ascertain the next higher values to enable your calculation.

This is sufficient for you to calculate the required increase, and thereby to complete your homework.

Is there some other point you would wish to raise?

----

**leo73pk** · 10-05-2006, 09:09 PM

Dear Brayn,

I'm not asking just to calculate the increase. Instead I'm simply looking for the formula which performs the following function;

In one file I have 2 columns one is for Heights (in meters) & other is for Equivalent Ltrs.
In second file we have two columns in first column if we insert height, the formula in second column returns the equivalent litres against the height we insert in the first column, from the file 1, But if the exact height not found then it determine between which two heights of file 1 our inserted value falls & then based on the following logic it return the result.
Assume in Column A we insert height 5.254 which is not exactly found in File 1, but this values lies between the two available heights i.e. 5.250 & 5.260 for which equivalent ltrs are given in File-1. therefore we need to do following calculations to get the litres at height 5.254
Take 5.250 & 5.260 as 5.254 lies between these two heights & take equivalent ltrs from column-b of file-1.

At Height 5.250 equivalent Ltrs are 5246640 & at 5.260 equivalent Ltrs are 5256799.

The Logical Formula which Calculate the liters at height 5.254 is as follows (from File-1)

=(5256799-5246640)/(5.260-5.250)*(5.254-5.250)+5246640
=5,250,704

Regards,

jbman

**Bryan Hessey** · 10-06-2006, 12:11 AM

Originally Posted by leo73pk

Dear Brayn,

I'm not asking just to calculate the increase. Instead I'm simply looking for the formula which performs the following function;

In one file I have 2 columns one is for Heights (in meters) & other is for Equivalent Ltrs.
In second file we have two columns in first column if we insert height, the formula in second column returns the equivalent litres against the height we insert in the first column, from the file 1, But if the exact height not found then it determine between which two heights of file 1 our inserted value falls & then based on the following logic it return the result.
Assume in Column A we insert height 5.254 which is not exactly found in File 1, but this values lies between the two available heights i.e. 5.250 & 5.260 for which equivalent ltrs are given in File-1. therefore we need to do following calculations to get the litres at height 5.254
Take 5.250 & 5.260 as 5.254 lies between these two heights & take equivalent ltrs from column-b of file-1.

At Height 5.250 equivalent Ltrs are 5246640 & at 5.260 equivalent Ltrs are 5256799.

The Logical Formula which Calculate the liters at height 5.254 is as follows (from File-1)

=(5256799-5246640)/(5.260-5.250)*(5.254-5.250)+5246640
=5,250,704

Regards,

jbman

The sought after correctly adjusted VLookup value is

=(5256799-5246640)/(5.260-5.250)*(5.254-5.250)+5246640

to relate everything back to your 2 knowns, base (=5,254) and table:

Please Login or Register  to view this content.

. . . quite honestly if I made a small error in that you might never find it, you'll just get low marks.

To work from a number of cells gives you a chance to detect where an error has occurred and to gain a basic understanding of what you are trying to achieve, rather than to ask for an enormous formula that you will never be able to understand, check, fix, modify nor explain.

This work is almost identical to your prior question relating to the adjustment of a VLookup'd figure to calculate the minor adjustment required where the matched figure was not equal the requested lookup.

The same formula applies, just a different table and a different base amount.

Does this answer your question?

-------

**leo73pk** · 10-07-2006, 10:21 PM

dear brayn,

the formula you proposed is not working.

can we use ceiling to find the upper & lower values w.r.t the known values

regards,

jbman

**Bryan Hessey** · 10-08-2006, 06:35 AM

Originally Posted by leo73pk

dear brayn,

who?

the formula you proposed is not working.

In what way, what have you entered as a formula and what result did it produce, what result did you expect from that portion of the formula?

You have a complete description of the parts of the formula, which bit doesn't perform in your situation as it was demonstrated in the .xls ?

can we use ceiling to find the upper & lower values w.r.t the known values

I am not sure what you are intending to do with the 'Ceiling' nor what you expect as an answer from the 'Ceiling' - but if it helps you then certainly you can.

The formula, as described, does work, and each part of the formula is described and demonstrated in the file.

If the 'joining together' of all portions of the formula has an error then you need to post the formula as you are using it so that it can be tested for error.

Did you enter the correct 'table-start' address, and specify the correct 'table'?

To get help you really need to be more specific about the portion you need help with, rather than a bland "it doesn't work" and expect a re-evaluation of your problem from scratch.

Which part is giving errors?

---

**leo73pk** · 10-09-2006, 07:18 AM

what do u mean by table-start? What is the difference between table & table start.

what i do understand that table-start is the first cell of the file h2.xls. & table means the entire table.

**Bryan Hessey** · 10-09-2006, 08:46 AM

Originally Posted by leo73pk

what do u mean by table-start? What is the difference between table & table start.

what i do understand that table-start is the first cell of the file h2.xls. & table means the entire table.

Table Start is the start of the table, and Table is the complete range of the table, both of these can be confirmed from the previously supplied file.

---