In this sales report, there is a specific pool of money that needs divided across six groups at varying percentages (which sum to 100%). However, due to the accounting rounding necessary, there often is a couple cent discrepancy depending on the total.
Example:
# TOTAL
total <- 12345.02
# PERCENT ALLOCATIONS
perA <- .12
perB <- .23
perC <- .22
perD <- .13
perE <- .11
perF <- .19
# ALLOCATIONS
amtA <- total * perA
amtB <- total * perB
amtC <- total * perC
amtD <- total * perD
amtE <- total * perE
amtF <- total * perF
These calculations are fine, but when accounting is applied, it will necessitate rounding to 2 decimal places as to be expected, during which not all funds are allocated.
Results:
library(formattable)
accounting(amtA) = $ 1,481.40
accounting(amtB) = $ 2,839.35
accounting(amtC) = $ 2,715.90
accounting(amtD) = $ 1,604.85
accounting(amtE) = $ 1,357.95
accounting(amtF) = $ 2,345.55
-----------------------------
sum = $12,345.00 =/= $12,345.02
I essentially want to know if there is a simple way to check the sum of the rounded allocations against the original total, treating the rounded numbers without considering that the precision lies underneath. From here, allocate any remaining funds in a specific order (E,A,C,B,D,F) if necessary.
Since I know which change will result in the error because my group of 6 & percentages are static, I have a simple way of checking the total if it ends in the issue cases and set rules for applying it.
library(dplyr)
library(stringr)
# OFF BY ONE CENT THAT NEEDS ALLOCATED
singlepos <- c("01","18",...)
# OFF BY TWO CENTS THAT NEED ALLOCATED
doublepos <- c("02","98",...)
# OFF BY ONE CENT THAT NEEDS UNALLOCATED
singleneg <- c("07","08",...)
amtE <- round(total * perE,2)
amtE <- case_when(
str_sub(as.character(amtE), starts = -2) %in% singlepos ~ amtE+.01,
str_sub(as.character(amtE), starts = -2) %in% doublepos ~ amtE+.01,
str_sub(as.character(amtE), starts = -2) %in% singleneg ~ amtE-.01,
.default = amtE
)
However this is far from ideal as there are 40 instances of sum errors for this particular group of 6. Perhaps some modulo division would be appropriate but, this Friday afternoon I am unable to wrap my head around possibly applying it to this situation.
Any help or tips would be greatly appreciated.
If I understand you correctly, you could allocate any remaining funds in the desired order using a for loop:
total <- 12345 + .02
per <- c(.12, .23, .22, .13, .11, .19)
amt <- round(total * per, 2)
delta <- as.integer(100 * (total - sum(amt)))
total - sum(amt)
#> [1] 0.02
# E -> A -> C -> B -> D -> F
for (i in c(5, 1, 3, 2, 4, 6)) {
if (delta > 0L) {
amt[[i]] <- amt[[i]] + .01
delta <- delta - 1L
} else if (delta < 0L) {
amt[[i]] <- amt[[i]] - .01
delta <- delta + 1L
} else {
break
}
}
library(formattable)
# Check
total - sum(amt)
#> [1] 0
accounting(amt)
#> [1] 1,481.41 2,839.35 2,715.90 1,604.85 1,357.96 2,345.55