Rounding to .25 Increments

Was working with someone this week that needed to calculate ‘Full-Time Equivalents’ from Part-Time jobs to meet some state requirements for grant money they manage.  For the last few years, their team has been downloading their year end surveys in a report, doing the math in excel, then manually updating records in Salesforce because a previous consultant said, ‘it can’t be done.’

Oh yeah? 

After some tinkering, this is the formula I came up with – I’m sure there are other ways to do this, I am definitely open to simpler methods, so please share in the comments!

Rounding to .25 increments

Numbers will round like below:
Less than .125 = .00
Between .125-.374=.25
Between.375-.624=.5
Between.625-.874=.75
Greater than .875=1.00

There are two variables you’ll need to define ahead of time:

#1: Number or Calculation being Divided
#2: Number or Calculation Dividing #1 by

With these two items, you should be able to plug them into the formula below to round to the nearest .25 increment using Mod(), MFloor(), and MCeiling() in a nested If() statement.

Mod() will find the remainder of Variable#1/Variable#2.  We then multiply the remainder by increments of 0.125 to determine which .25 increment we should round to. MFloor() rounds down to the nearest whole number & we add the appropriate .25 increment, while the final MCeiling() rounds up when appropriate.

/*If remainder is 0, we don’t need to do anything, just divide Var#1/Var#2 */
IF(Mod((Variable#1),Variable#2)=0, (Variable#1)/ Variable#2,

/*If remainder is less than .125 of Var#2, round down to the whole number */
IF(Mod((Variable#1),Variable#2)<0.125*Variable#2, MFloor((Variable#1)/Variable#2),

/*If remainder is greater or equal to .125 but less than .375 of Var#2, round down to the whole number & add .25 */
IF(AND(Mod((Variable#1),Variable#2)>= 0.125*Variable#2, Mod((Variable#1),Variable#2)<0.375* Variable#2, MFloor((Variable#1)/Variable#2)+0.25,

/*If remainder is greater or equal to .375 but less than .625 of Var#2, round down to the whole number & add .5 */
IF(AND(Mod((Variable#1),Variable#2)>=0.375*Variable#2, Mod((Variable#1),Variable#2)< 0.625* Variable#2, MFloor((Variable#1)/Variable#2)+0.5,

/*If remainder is greater or equal to .625 but less than .875 of Var#2, round down to the whole number & add .75 */
IF(AND(Mod((Variable#1),Variable#2)>= 0.625*Variable#2, Mod((Variable#1),Variable#2)<0.875*Variable#2, MFloor((Variable#1)/Variable#2)+0.75,

/*The only remaining possibility is the remainder is greater than or equal to .875 of Var#2, so we round up to the next whole number */
MCeiling((Variable#1)/Variable#2) )))))

Example Use Cases

Round Minutes Between Two Date/Time Fields to .25 Hour Increments

Variable#1= (Date/Time1-Date/Time2)*24Hours*60Minutes
Variable#2=60Minutes

IF(Mod(((LastModifiedDate-CreatedDate)*24*60),60)=0, ((LastModifiedDate-CreatedDate)*24*60)/ 60,
IF(Mod(((LastModifiedDate-CreatedDate)*24*60),60)<0.125*60, MFloor(((LastModifiedDate-CreatedDate)*24*60)/60),
IF(AND(Mod(((LastModifiedDate-CreatedDate)*24*60),60)>= 0.125*60, Mod(((LastModifiedDate-CreatedDate)*24*60),60)<0.375* 60, MFloor(((LastModifiedDate-CreatedDate)*24*60)/60)+0.25,
IF(AND(Mod(((LastModifiedDate-CreatedDate)*24*60),60)>=0.375*60, Mod(((LastModifiedDate-CreatedDate)*24*60),60)< 0.625* 60, MFloor(((LastModifiedDate-CreatedDate)*24*60)/60)+0.5,
IF(AND(Mod(((LastModifiedDate-CreatedDate)*24*60),60)>= 0.625*60, Mod(((LastModifiedDate-CreatedDate)*24*60),60)<0.875*60, MFloor(((LastModifiedDate-CreatedDate)*24*60)/60)+0.75,
MCeiling(((LastModifiedDate-CreatedDate)*24*60)/60))))))

Calcluate FT Equivalents in .25 Increments from PT Jobs&AvgWeeklyHours

Variable#1= #PartTimeJobs*#AvgHoursWorked*52Weeks
Variable#2=2080Hours (number of working hours in a year, 40hrs/wk *52 weeks)

IF(Mod((PTJobs__c*WeeklyHours__c*52),2080)=0, (PTJobs__c*WeeklyHours__c*52)/ 2080,
IF(Mod((PTJobs__c*WeeklyHours__c*52),2080)<0.125*2080, MFloor((PTJobs__c*WeeklyHours__c*52)/2080),
IF(AND(Mod((PTJobs__c*WeeklyHours__c*52),2080)>= 0.125*2080, Mod((PTJobs__c*WeeklyHours__c*52),2080)<0.375* 2080, MFloor((PTJobs__c*WeeklyHours__c*52)/2080)+0.25,
IF(AND(Mod((PTJobs__c*WeeklyHours__c*52),2080)>=0.375*2080, Mod((PTJobs__c*WeeklyHours__c*52),2080)< 0.625* 2080, MFloor((PTJobs__c*WeeklyHours__c*52)/2080)+0.5,
IF(AND(Mod((PTJobs__c*WeeklyHours__c*52),2080)>= 0.625*2080, Mod((PTJobs__c*WeeklyHours__c*52),2080)<0.875*2080, MFloor((PTJobs__c*WeeklyHours__c*52)/2080)+0.75,
MCeiling((PTJobs__c*WeeklyHours__c*52)/2080))))))