Excel Formula

Question

Hello.

I want to make a points based league table for my students.

So I'd like the points cell to increase by 1 when cell B3 increases by 1, and for the points cell to increase by 2 when cell C3 increases by 1, and for the points cell to increase by 3 when cell D3 increases by 1, and for the points cell to increase by 5 when cell E3 increases by 1.

At the same time, I'd like for the entire table to be shifting up and down, according to rank from highest points to lowest points, just like the premier league table in English football.

Please tell me the formulas I need for this.

Thank you.

Kind regards,

Ilyas Aziz

Answer 1

when B3, C3, D3 or E3 increase?

100% no.

But if you say we should assume the initial value 0 in each, we can give. e.g. one point for each cell > 0.

=SUM(--(B3:E3>0))

Andreas.

Answer 2

The thing is, the original value of the cell B3, will be 0, as the points will increase as they progress throughout the year.

So is there no way of telling the points cell to increase by a certain amount when B3, C3, D3 or E3 increase?

Answer 3

points cell to increase by 1 when cell B3 increases by 1

That's not possible using formulas, because we do not know the previous value of B3.

For example if the value of B3 in my file is 123, you do not know if it was increased or decreased.

Theoretically, you have to make a copy of the whole sheet to keep the old values as backup. Now in your sheet if a cell is changed you can use VBA and look into the backup sheet into the same cell, e.g. in there is 95 and you know the value is increased. At this point you can add 1 to the cell that contains the points.

Possible, but you lost the ability to sort / insert / delete rows (any action that moves cells).

Andreas.