Excel OfficeTo randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions. Formula =ROUNDUP(RANK(A1,randoms)/size,0) Explanation In the example shown, the formula in D5 is: =ROUNDUP(RANK(C5,randoms)/size,0) which returns a group number… read more »
To get the next payday – assuming a biweekly schedule, with paydays on Friday – you can use a formula based on the CEILING function. Formula =CEILING(date+1,14)-1 Explanation In the example shown, the formula in C6 is: =CEILING(B6+1,14)-1 Note: this formula assumes Excel’s default 1900 date system. How this formula works This formula depends on… read more »
This tutorials shows how to Round to nearest 5 in Excel. If you need to round a number to the nearest multiple of 5, you can use the MROUND function and supply 5 for number of digits. Formula =MROUND(number,5) Explanation In the example, cell C6 contains this formula: =MROUND(B6,5) The value in B6 is 17… read more »
This tutorials shows how to Round time to nearest 15 minutes in Excel. To round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. Formula =MROUND(time,”0:15″) Explanation In the example shown, the formula in C6 is: =MROUND(B6,”0:15″) How this formula works MROUND rounds to… read more »
This tutorials shows how to Round by bundle size in Excel. To round up to the next bundle size, you can use the CEILING function which automatically rounds up away from zero. To round up to the next bundle size, you can use the CEILING function which automatically rounds up away from zero. Formula =CEILING(number,bundle)/bundle… read more »
This tutorials shows how to Round a number up to next half in Excel. If you need to round a number up to the next half, you can use the CEILING function, which always rounds up based on a supplied multiple. Formula =CEILING(number,0.5) Note that MROUND also rounds based on a supplied multiple, but it always rounds to… read more »
This tutorials shows how to Round a number up to nearest multiple in Excel. If you need to round a number up to the nearest specified multiple (i.e. round a number up to the nearest dollar, up to the nearest $.25, up to the nearest multiple of 5, etc.) you can use the CEILING function. Formula =CEILING(number,multiple)… read more »
This tutorials shows how to Round a number down to nearest multiple in Excel. If you need to round a number down to the nearest specified multiple (i.e. round a number down to the nearest dollar, down to the nearest $.25, down to the nearest multiple of 5, etc.) you can use the FLOOR function. Formula =FLOOR(number,multiple)… read more »
This tutorial shows how to Shade alternating groups of n rows in Excel using the example below; Formula =ISEVEN(CEILING(ROW()-offset,n)/n) Explanation To highlight rows in groups of “n” (i.e. shade every 3 rows, every 5 rows, etc.) you can apply conditional formatting with a formula based on the ROW, CEILING and ISEVEN functions. In the example… read more »
This tutorial shows how to Running count group by n size in Excel using the example below; Formula =CEILING(COUNTA(expanding_range)/size,1) > Explanation To creating a running count of groups of a variable size, you can use the COUNTA and CEILING function. In the example shown, C5 contains this formula: =CEILING(COUNTA($B$5:B5)/size,1) where “size” is the named range F4. How… read more »