math this math
March 8, 2024 9:31 AM Subscribe
I need help building an efficient (1 step) excel formula to do a very specific piece of math. Please and thank you!
The use case is payroll data.
The system we use can only accept salary numbers in a per-check amount: annual divided by 24 for people paid semi monthly, annual divided by 12 for people paid monthly. I need to translate an annual salary into a 1/24th or 1/12th amount without cheating people out of pennies. (It's fine for the company to lose pennies.)
For example:
You make $52,000 a year. If I put $2166.66 into the system, it will say your annual salary is $51,999.84. This is shitty. If I put $2166.67 into the system, it will say your salary is $52,000.08. This is preferred.
Adding a penny across the board doesn't work because some salaries divide evenly and don't need it, and some salaries aren't tidy round numbers and a penny won't do it. Similarly, rounding up to a whole dollar also doesn't work because... some salaries aren't tidy round numbers.
Basically: I need to get as close as possible to the requested salary number without ever going under the stated salary number. I'm sure there's a way to do this but I don't have the time right now to sit and figure it out. Thank you!
The use case is payroll data.
The system we use can only accept salary numbers in a per-check amount: annual divided by 24 for people paid semi monthly, annual divided by 12 for people paid monthly. I need to translate an annual salary into a 1/24th or 1/12th amount without cheating people out of pennies. (It's fine for the company to lose pennies.)
For example:
You make $52,000 a year. If I put $2166.66 into the system, it will say your annual salary is $51,999.84. This is shitty. If I put $2166.67 into the system, it will say your salary is $52,000.08. This is preferred.
Adding a penny across the board doesn't work because some salaries divide evenly and don't need it, and some salaries aren't tidy round numbers and a penny won't do it. Similarly, rounding up to a whole dollar also doesn't work because... some salaries aren't tidy round numbers.
Basically: I need to get as close as possible to the requested salary number without ever going under the stated salary number. I'm sure there's a way to do this but I don't have the time right now to sit and figure it out. Thank you!
Response by poster: You know I swear last year when I was working on this same data project there were situations where that didn't work, but now I can't find a single place it doesn't. Thank you! 🤦
(I think last year it's because managers were reporting salaries with dollars and cents, but I think we fixed that on the front end this go around... ??.?..)
posted by phunniemee at 9:47 AM on March 8
(I think last year it's because managers were reporting salaries with dollars and cents, but I think we fixed that on the front end this go around... ??.?..)
posted by phunniemee at 9:47 AM on March 8
I am paid a salary 24 times per year, and occasionally my check gets a penny added to it to make it come out right.
To get the check amount, you would use ROUNDDOWN in that same formula above and then in another cell that is formatted as a number and not currency you use =100*MOD(salary,0.12). That tells you how many checks need to have a penny added to them. MOD gives you just the remainder of a division problem, like Mod(53,10) will give you 3. I think overpaying people by a few cents is obviously a better solution, but this is how to calculate it if you must pay people an exact amount.
posted by soelo at 1:51 PM on March 8
To get the check amount, you would use ROUNDDOWN in that same formula above and then in another cell that is formatted as a number and not currency you use =100*MOD(salary,0.12). That tells you how many checks need to have a penny added to them. MOD gives you just the remainder of a division problem, like Mod(53,10) will give you 3. I think overpaying people by a few cents is obviously a better solution, but this is how to calculate it if you must pay people an exact amount.
posted by soelo at 1:51 PM on March 8
Our new-fangled LLMs are really good at helping with spreadsheets. I asked google’s Gemini what you asked above, and here’s the formula:
posted by notyou at 4:45 AM on March 11
=IF(MOD(A1,12)=0,And the explanation:
FLOOR(A1/12,0.01),
FLOOR(A1/24,0.01))
+ IF(MOD(A1,12)=0,
A1 - 12*FLOOR(A1/12,0.01),
A1 - 24*FLOOR(A1/24,0.01))/24
Explanation:Haven’t tested it.
IF(MOD(A1,12)=0...): This section checks if the salary is divisible by 12 (paid monthly). If so, it calculates the per-check amount for monthly. If not, it does it for semi-monthly.
FLOOR(A1/12,0.01) or FLOOR(A1/24,0.01): This rounds the exact per-check amount down to the nearest cent.
A1 - 12FLOOR(A1/12,0.01) or A1 - 24FLOOR(A1/24,0.01): This calculates the discrepancy (the 'lost' pennies)
... /24: The last part adds the discrepancy to one of the semi-monthly payments to ensure it sums to the correct annual amount.
posted by notyou at 4:45 AM on March 11
You are not logged in, either login or create an account to post comments
posted by mullacc at 9:34 AM on March 8 [10 favorites]