# 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 +5In 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

#1
October 26, 2017 at 08:37:21
 You're looking for the MOD function.

#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))+df6BV6=Previous Total DF6=This Month Points.=IF(13>10,SUM(13-10))+2 = new total of 5

#3
October 26, 2017 at 10:15:52
 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```message edited by DerbyDad03