Home |
Search |
Today's Posts |
#1
|
|||
|
|||
output based on data in a graph
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? |
#2
|
|||
|
|||
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. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=467718 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sharing data across worksheets within a workbook based on identifi | Excel Discussion (Misc queries) | |||
Graph with variable data length | Excel Discussion (Misc queries) | |||
How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel | |||
REPOST: How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |