Decay formula

Cleo1

New Member
Joined
Dec 11, 2007
Messages
7
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
 
Upvote 0
Sorry, I misread you.

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

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

HTH

thumbup.gif
 
Upvote 0
ADVERTISEMENT
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=?
 
Upvote 0
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)
 
Upvote 0
ADVERTISEMENT
Hello,

try

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

gives 150 for your example
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top