% increase when previous result is 0

[Rich]

We have a scoring system at work that measures our team's success by %
increase on
last year, for various functions. Each of 9 teams is then allocated points ,
8 for biggest % increase, down to 0 for lowest

For a particular KPI, my last year my team had a zero result, this year
we're on 3.

The formula can't handle 0 as a starting point, so we end up with 0 points,
and will no no matter how well we do this year...

So, in the real world, what is our poercentage increase if we had 0 last
year, 3 this year ?

In excel, what is the fairest formula to calculate this ?

 

[Andy Brown]

Don't really follow. With Last Year in A1 and This Year in B1 ;

=(B1-A1)/8%

in C1 returns 37.5 for 0 to 3, -100 for 8 to 0, 12.5 for 0 to 1, etc.

Anything like what you need?

HTH,
Andy

 

[Myrna Larson]

Let's say the "function" you are talking about is sales. I assume that when you say that last
year you had a zero result, you mean you were allocated 0 points because you had the smallest
increase in sales compared with other teams.

It's the change in SALES that you must calculate, rank, and assign points. If you had $0 sales
last year, then you have a problem calculating a percentage increase. That isn't the case, is
it?

Let's say your layout is one row for each team with headers in row 1. There are 8 teams in rows
2:9 (headers in row 1). Put the team names in column A. In B put previous year's sales, in C,
this year's sales, in D, the formula =C2/B2-1, formatted as percent. In column E,
=RANK(D2,$D$2:$D$9). In column F, the formula to allocate the points.

Note that the new points are based on change in SALES, not on a change in points or rank, from
one year to the next.

If you don't know the sales for the other teams, then I don't see a way to calculate this.

And if your *sales* were truly $0 last year, then you need to consult the people who devised the
system to see how to handle this situation.

 

[Ken Wright]

LOL - tell him that starting from 0 last year put you in an unfavourably good position, and that
you should have to use a nominal 0.00000001 for last year to even it up somewhat.

Seriously though, if % increase is the deciding factor then you win. Any mathematician should be
able to demonstrate that to him in seconds. Ask him to link the bonus to the increase %

Show him the table I gave you, he can't argue it. It proves beyond a doubt that your % increase
is way way above 300%.

 

[Myrna Larson]

The percentage increase is in fact infinite.

 

[Ken Wright]

I know, but Excel can't put that onto a sheet, and his boss won't believe it unless he sees it in
black and white. The table I gave him though, that shows % increase tending to infinity as last
years increase tends to 0 should go someway to convincing him hopefully. I'd be real upset if
this was the criteria and he told me I had lost, especially given that my score would be
unbeatable.