Solved excel reduce by 10 if greater than 10

October 26, 2017 at 07:19:17
Specs: Windows 10
i run a lateness point system for staff.
Every 10 late points they get a salary cut (works like a charm)
Its an accumulative value per year.

eg late points Jan = 6 feb + 2 mar +5
In march the total late points would be 13.
Salary would be cut in march.

I want in april the balance to start off with 3 as the 10 points will be cut with the march salary.

im trying to work out an IF formula to do that.

eg if previous total greater than 10, reduce by 10 and add "current month points"

Any help please.

message edited by MrZen


See More: excel reduce by 10 if greater than 10

Reply ↓  Report •

#1
October 26, 2017 at 08:37:21

Reply ↓  Report •

#2
October 26, 2017 at 09:24:41
Actually after much much more trial and error I think I have cracked it

=IF(bv6>10,SUM(bv6-10))+df6

BV6=Previous Total DF6=This Month Points.

=IF(13>10,SUM(13-10))+2 = new total of 5


Reply ↓  Report •

#3
October 26, 2017 at 10:15:52
✔ Best Answer
Maybe I'm missing something. What formula are you using in BV6 to get the Previous Total? Or, asked differently, what values does your "Previous Total" consist of?

If BV6 sums the months, then I agree that SUM(Jan, Feb, Mar) = 13, so your formula should should be correct at the end of April. i.e. If April = 2, final result should be 5.

However, what happens a few months down the line when SUM(Jan, Feb, Mar, Apr, May, June) = 24?

24 - 10 + 2 = 16 (i.e. The reset to <10 is lost)

To expand on Razor2.3's suggestion, perhaps you want to try something like this, where BU2:BU13 contains the monthly points for an individual:

=MOD(SUM(BU2:BU13),10)

For this data the result will be 5 at the end of April:

        BT        BU      
1     Month      Points    
2      Jan         6             
3      Feb         2             
4      Mar         5              
5      Apr         2

For this data the result will 4 (not 16) at the end of July:

        BT        BU      
1     Month      Points    
2      Jan         6             
3      Feb         2             
4      Mar         5              
5      Apr         2
6      May         2
7      Jun         5
8      Jul         2

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
October 26, 2017 at 18:07:13
Thanks, I've actually never used MOD before - Ill give that a try too.

Reply ↓  Report •

Ask Question