Calculating a value without lookup table

[kwinters360]

I would like to know is if it is possible to find a exact value for a exact size pipe if the values are not in the lookup table.

For instance, if 100" pipe is $400, 200" pipe is $700, 250" pipe is $900. How much would it be 217"? The answer should be something close to $775....but I don't know how to calculate exactly, or at least a good rough estimate.

[pjwhitfield]

off the top of my head, you could:

1. Read the lookup table into an array
2. Find the last size lower than the requested one and get its cost to give you COST1
3. Take the cost from the next item in the array to give COST2
4. Calculate the new cost based on those 2

[Sam Capricci]

I guess how i'd do it is to take the difference between 200 and 217 (17) and the difference between 250 and 200 being 50, assuming it is a linear increment 17/50 = 0.34 so take the difference between the prices of 200" pipe and 250" pipe which is $200, multiply that by the 0.34 which is $68 and add it to the lower end price of $700 so it would be $768.

[newdoverman]

The price will depend upon how prices other than for lengths of 100", 200", 250" are handled. Are the prices in blocks...1"-99", 100"-199" etc. or is there a linear progression between the major values? The method makes a difference. Based on price per inch the price could be $759.50 based on the 200" pipe. The price would be $768 if you use a proportional difference between major price points like Sambo Kid did.

[joeu2004]

I would like to know is if it is possible to find a exact value for a exact size pipe if the values are not in the lookup table. For instance, if 100" pipe is $400, 200" pipe is $700, 250" pipe is $900. How much would it be 217"?

Try:

Please Login or Register  to view this content.

That assumes you have 217 in A7, and you have 100, 200, 250 and 251 in A2:A5, and 400, 700, 900 and 900 in B2:B5.

The formula interpolates linearly between two adjacent rows, the first row being the closest to the value in A7, but no more than that value.

I assume A4 contains the largest possible value. The extra row in A5:B5 is needed to simplify the OFFSET expression. A5 can be any value larger than A4; for example, =A4+1. B5 can be any value; for example, =B4.

[kwinters360]

Thank you. This is what I was looking for.