# Decay formula

#### Cleo1

##### New Member
So i'm making a table that has points for people and attendance Columns. I need a decay column to calculate new points based on how people attended the event. Like if attendace is 91% -100% then they will only decay 10% of their original points and 81%-90% is 20% etc. etc.
Person Points Attendace Decay
A 500 60% ?
B 200 90%
C 100 100%
D 400 100%

Thans so much you guys are always such great help!!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi,

do you need perhaps something like this:

=((B1-1)*A1)+A1

B1 -> containing attendance
A1 -> containing points

To establish thresholds (81-90, 91-100), use the ROUNDDOWN()-function:

=((ROUNDDOWN(B1,1)-1)*A1)+A1

HTH

Hello,

is this giving expected results?

=B2*(1-FLOOR(C2-0.01,0.1))

Actually I think I may not have explained this correctly.

X person has 500 points they have 75% attendace.75% falls between 71-80 and therefore is 30% penalized. So I need the decay colums C1 to be 500*30%=150. Do i have to make a separate list for attendace values?

Attendace is viewed as following:

91-100=10%
81-90=20%
71-80=30%
61-70=40%

A1 =points
B1 = Percentage attended
C1=?

Hi,

In that case, use:

=((1-ROUNDDOWN(B1,1))*A1)

Only drawback is that 70% is considered to be in the 70-80-slot (30% penalty), and not in the 60-70-slot (40% penalty)

Hello,

try

=A1*(1-FLOOR(B1-0.01,0.1))

Hello,

try

=A1*(1-FLOOR(B1-0.01,0.1))

Worked!! definetly not only a drafter!!

Cleo1,

Excel Workbook
ABCD
1PersonPointsAttendanceNew Points
2A50060.00%250
3B20090.00%160
4C100100.00%90
5D400100.00%360
Sheet1

The formula in cell D2 (copied down):
=B2-(B2*LOOKUP(C2,{0.1,0.11,0.21,0.31,0.41,0.51,0.61,0.71,0.81,0.91},{1,0.1,0.2,0.3,0.4,0.5,0.4,0.3,0.2,0.1}))

Have a great day,
Stan

Replies
2
Views
245
Replies
15
Views
482
Replies
0
Views
314
Replies
0
Views
285
Replies
3
Views
490

1,196,414
Messages
6,015,129
Members
441,874
Latest member
saustark

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back