Hi everybody!
I need to fill a series starting at 0.02% and ending at 10% (discounts) in a specific range which may be changed, but not in fixed increments (step values) nor in a straight line (linear) nor as a series showing a gradual growth in the values.
Discounts are granted to a product based on the cost of the product and I need to come up with discounts for these products based on the product price. The requirement is that the discount must start at a low base of 0.02% for a product costing $950 (starting price) and grow to a maximum of 10% for a product costing $5,500 (maximum price).
I'd entered these values in range A2 to A912, starting at 950 and increasing each next value by 5 up to 5500. I entered 0.02% in B2 and 10% in B912 and used Excel's Edit/FILL/Series functionality to fill the series between the two values, but it does not provide what I need with its linear and growth (starting slow and increasing in the end) options, with or without ticking the "trend" box.
Excel's help menu shows a logarithmic trendline to display the characteristics of the way my data should display in a chart - growing quickly in the beginning and flattening out later.
I used the LN (Natural logarithm) function to get the natural logarithms of column B, but the negative numbers do vreate a nice "inverse curve" graph, but the logarithmic values do nothing to help me with the discount percentages!
I need something (formula copied down, VBA, or whatever) which will create a series of discount percentages that exhibits the properties shown in a logarithmic line.
I hope I made myself understood - and that someone has a brilliant solution!
With kind and hopeful regards
Harry
I need to fill a series starting at 0.02% and ending at 10% (discounts) in a specific range which may be changed, but not in fixed increments (step values) nor in a straight line (linear) nor as a series showing a gradual growth in the values.
Discounts are granted to a product based on the cost of the product and I need to come up with discounts for these products based on the product price. The requirement is that the discount must start at a low base of 0.02% for a product costing $950 (starting price) and grow to a maximum of 10% for a product costing $5,500 (maximum price).
I'd entered these values in range A2 to A912, starting at 950 and increasing each next value by 5 up to 5500. I entered 0.02% in B2 and 10% in B912 and used Excel's Edit/FILL/Series functionality to fill the series between the two values, but it does not provide what I need with its linear and growth (starting slow and increasing in the end) options, with or without ticking the "trend" box.
Excel's help menu shows a logarithmic trendline to display the characteristics of the way my data should display in a chart - growing quickly in the beginning and flattening out later.
I used the LN (Natural logarithm) function to get the natural logarithms of column B, but the negative numbers do vreate a nice "inverse curve" graph, but the logarithmic values do nothing to help me with the discount percentages!
I need something (formula copied down, VBA, or whatever) which will create a series of discount percentages that exhibits the properties shown in a logarithmic line.
I hope I made myself understood - and that someone has a brilliant solution!
With kind and hopeful regards
Harry
