output based on data in a graph

[Patrick C.]

I'm trying to write a formula that will predict any number along a curve if I
provide the x coordinate. Specifically, I'm a salesman and I have a product
that is less expensive the more you buy. in other words, the price per unit
becomes flatter in higher quantities, but is very steep in lower quantites.
I have lots of data points on this curve at general quantites: 1000, 2000,
3000 etc. I want to write a formula that calculates the coresponding
price/unit for whatever quantity I input (eg: 2374) based on the curve. Any
suggestions?

[MrShorty]

It sounds like you have a decent data table to work with. An interpolation function sounds like a good candidate for your situation. Unfortunately, Excel doesn't have a built in interpolation function (Quattro Pro does if you have access to it). You can build an interpolation function either with spreadsheet functions or in VBA, both have been discussed before by people better qualified than me to build such functions.

I don't know how useful it would be, but VLOOKUP would be able to return the price/unit for each of the main data points. Example, if you wanted to lookup 2500, VLOOKUP would return the price/unit for 2000.

You might also be able to regress the data (using LINEST or other regression functions) with some suitable function; trouble is selecting an appropriate function. Based on your description of the data, something like y=f[sqrt(x)] or y=f[log(x)] may be suitable, but I can't say for sure exactly what form those functions would take without the actual data and time to play with the regression.

Of course, if you have access to how the supplier determines price/unit (or total price), then you can use that.

Not much help, but I hope it's a start for you.