It’s been a long time since I took algebra and I need help creating a formula in Excel.
I need to award points for an achievement based on the amount of time it takes to complete, but the value should increase exponentially, for example:
If it takes 1 minute, 1 point should be awarded.
If it takes 2 minutes, 3 points should be awarded.
If it takes 3 minutes, 7 points should be awarded.
How do I write a formula like this? Thanks.
I’m assuming that you want the curve to also go through (0, 0). Well, no exponential function will work. But the equation y = (1/6)x[sup]3[/sup]+(5/6)x works pretty well.
The longer it takes, the more points are awarded?
x^2-(x-1) works for the values you provided. Do you need help with how to implement this in excel too?
In Excel, you can make a scatter plot with the data, connect them with a trend line, and then have it spit out the formula.
Using MatLab, I got:
.75x^2 + .05x + .05, if you want to go through 0,0 or
x^2 - x + 1 if you don’t. (Same as Snarky’s answer, different form)
The MatLab code is pretty simple:
x = [1 2 3];
y = [1 3 7];
p = polyfit (x,y,2);
fprintf(’%10.5f’,p)
ETA: The fprintf command is because I needed more sig figs for a program I wrote this morning.
Actually, y = 2[sup]x[/sup]–1 would fit the OP’s numbers.
(In Excel, I guess the formula would be 2^CELLNUMBER - 1)
This would go through (0, 0).
EXPONENTIAL, folks, not polynomic. Stew’s asking for a function like:
y = 2[sup]x[/sup] - 1
which, by the way, will give (0,0) as well as (1,1), (2,3), and (3,7).
In Excel, if your “minutes” are in cell A1, try:
= POWER(2, A1) - 1
Or, on preview, what Thudlow said.
The polynomials given all increase EXPONENTIALLY. The OP asked for the values given by the formula to increase exponentially , which a polynomial accomplishes nicely. They did not ask for an exponential formula, which is your (equally valid) interpretation.
ETA: OP, to add the trendline, just right click the line the scatter plot made.
Yes, I could use some help. And actually, as I look at what I’m trying to achieve, I really ought to subtract a certain number of points from a maximum possible allocation. For example, time portion of the evaluation would offer a maximum of 5 points:
If the test took 1 minutes, the user would receive all 5 points.
If the test took 2 minutes, the user would receive 3.5 points.
If the test took 3 minutes, the user would receive 1.5 points.
If the test took any longer than 4 minutes, the user would receive 0 points.
The numbers in the example are pulled from thin air. Basically, each extra second should be more “expensive” than the previous second.
Thanks for all the help.
Here’s how to do it step by step, so you can plug in whatever numbers you’d like later:
Put the numbers in Excel like this:
1 5
2 3.5
3 1.5
Highlight all six cells (or however many you end up using).
Click Chart Wizard (it’s on one of the top toolbars).
Select XY (Scatter), then select “Scatter with data points connected by smoothed lines.” as the sub-type. Hit “Next”, then “Finish”.
Right click on the line, and hit “Add Trendline”
From there, you can select polynomial, exponential, or power.
Over the range of numbers you’re using, though, the chart is going to look pretty linear. It sounds like you want a logarithmic plot, where the points drop off very fast and then level out? With the data points you have right now, the graph is y = -0.25x2 - 0.75x + 6, which is pretty close to linear over the range you’re working with.
Um, in what sense do the polynomials increase exponentially? Is “exponentially” meant to be taken as simply a synonym for “superlinearly”?
Seems to me 2^x - 1 is the nicest fit to the OP’s numbers. The first minute worth 1 point, the next worth 2, the next worth 4, the next worth 8, and so on, each worth twice the last one. The quadratic provided doesn’t go through (0, 0), as one might imagine necessary, and the cubic is just uninsightful mechanical application of n+1 points -> unique n degree polynomial fitting.
Exponential formulas increase exponentially. Polynomial formulas increase polynomically. Polynomial functions do not increase exponentially.
It sounds like, from his later post, that the OP might be perfectly happy with a polynomic function, in which case I would suggest an Excel formula like:
=IF(A1<1, 5, IF(A1>4, 0, 5-(A1-1)^2*5/9))
Which just fairs in a parabola between 1 and 4.
I’m guessing that, if the OP were asked, having each minute worth twice as much as the last would be right up his alley…
[I’m certain he wants 0 points for 0 minutes total, in which case the quadratic provided (another mechanical application of polynomial fitting) will not do.]
ETA: Oh, I missed a post in the middle where he stated he wants to subtract points from a maximum… ignore me.
Why ignore you? It works equally well, just subtract from the maximum value. In excel you can just toss an IF statement in there where if the resulting value is less than zero, the cell value will be zero.
The numbers I gave are totally made up. I don’t want them hard coded, because I want to be able to easily fiddle with them.
Briefly, what I’m doing is creating a formula to award a multi-million dollar contract. Bidders will submit a device which will be tested along several parameters, each worth a certain value*. So for example, a device will have 30 minutes to complete its task, and if it takes that long or longer, it will receive 0 points. The bidder will receive the full 5 points if its device completes the task in (an obviously impossible) 0 seconds. As a result, I don’t want to penalize anyone too much for taking 5 minutes, but 20 minutes should incur a rather harsh penalty.
However the formula is written, it needs to be flexible so the committee can easily change the maximum time permitted to 40 minutes and the maximum points awarded to 10.
Sorry I was so obtuse before. I didn’t think the answer would be so complicated. Thanks again for all the help.
*Ideally, what I’d like to do is divide 100 points between all the categories with the lowest score winning the contract
Indeed. It’s just that the brunt of the “Well, c’mon, the OP’s numbers are practically begging to be extrapolated as 2^x-1” argument was blunted, and so I felt silly.
Try this:
You have four parameters:
As a simple function, try:
=IF(D1>A1,0,B1-(D1/A1)^(C1)*B1)
This will give a value of maximum points at zero time, and a value of 0 at anything over the maximum allowable time. In between, it will give an increasingly harsh penalty for increasing time (with C1=2). If the penalty is too harsh, decrease the value in C1; if it’s not harsh enough, increase the value (you can use decimals here).
This is essentially the simplest function that does what you want (although a true exponential might be just as simple).
…
This is GREAT! Thanks.