Hey All,
First thanks for any help you can offer. I think I have the functions I need, I'm just not sure how to properly arrange them.
I want to create a sliding scale to calculate a product markup.
Anything under $5 is *2. Anything over $15 is *1.45. Everything inbetween is calculated based on price. I believe I need to use the forecast function on a table like below.
5 15 2 1.45
SO if my Cost" cell is A1 In my results cell I should have
=FORECAST(a1, $C$2:$D$2,$C$1:$D$1)
correct?
is this an excel question?
if so, it will be difficult to give an appropriate solution without:
(a) knowing what version of excel you have (currently your profile says "docs", and im not familiar with that version of excel)
and
(b) a sample file with some sample data (and manually calculated results) illustrating the enquiry you would like help with
As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.
And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]
I don't know if any of us on this side of the internet can declare this "correct." The proposed FORECAST() formula will:
1) Fit the two data points to a linear/straight line equation y=mx+b
2) Then take the given value for x, plug into the formula, then output y.
If that is what you want to happen, then this formula is correct. If you want something different to happen, then this formula is not correct.
Have you tried the formula with different inputs? Do you like the results it gives you?
Originally Posted by shg
The formula you suggest seems to give the result you want. I don't know how you want it to behave if you enter a value below 5 or above 15.
You can do it without any function; it's a simple interpolation problem:
=(($A$1-$C$1)*$D$2+($D$1-$A$1)*$C$2)/($D$1-$C$1)
Will give the same result as the FORECAST formula.
Thank you for the replies.
Sorry I was rushed and didn't attach a worksheet.
I had put it in a sheet, but the result ends up being off.
In my "cost" I enter 3.30 - I expect the result to be $6.60 (200% of cost).
My formula currently outputs 2.09
I enter 16 in my cost, and I expect the result to be 23.20 (145% of cost) but the result I currently have is 1.39.
Thanks again for any help!
I think the workbook is attached
That doesn't sound like what the FORECAST() function will do. The FORECAST() function might still be part of the solution, but we need a better description of the desired algorithm.
My guess:
1) anything below 5, and the multiplier should be 2 (or 200%).
2) anything above 15, and the multiplier should be 1.45 (or 145%).
3) in between 5 and 15, a linear??? (y=mx+b) interpolation based on the above values. 10, for example, would return 1.725.
If so, then I would expect something like =MIN(2,MAX(1.45,FORECAST(...)) should return the correct multiplier, according to my guess at the calculation scheme. If I got it right, it's because I was lucky enough to guess right at the desired calculation scheme. If I got it wrong, we would need more information about the calculation scheme in order to come up with a suitable formula.
If you want to have hard limits then you can use MAX and MIN.
For example:
=FORECAST(MAX(MIN(A1,15),5), $C$2:$D$2,$C$1:$D$1)
Will use the value of A1 in your FORECAST formula, but limit it to a MIN of 5 and a MAX of 15. It makes the formula a bit longer, but you can link this to the highest and lowest values in your range like this:
=FORECAST(MAX(MIN(A1,MAX($C$1:$D$1)),MIN($C$1:$D$1)), $C$2:$D$2,$C$1:$D$1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks