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. A
interpolation function sounds like a good candidate for your situation
Unfortunately, Excel doesn't have a built in interpolation functio
(Quattro Pro does if you have access to it). You can build a
interpolation function either with spreadsheet functions or in VBA
both have been discussed before by people better qualified than me t
build such functions.

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

You might also be able to regress the data (using LINEST or othe
regression functions) with some suitable function; trouble is selectin
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'
say for sure exactly what form those functions would take without th
actual data and time to play with the regression.

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

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