Greetings,
I came up with a formula that calculates Net Pay when you subtract Earnings from Deductions.
I now need assistance with a formula that Calculates Earnings and Deductions when the Net Pay figure has already been established.
Attached is a spreadsheet of the Net Pay calculator.
Net Pay Calculator.xlsx
Please Login or Register to view this content.
in E13
=SUMPRODUCT(($E$4>=$A$7:$A$10)*($E$4-$A$7:$A$10),$F$7:$F$10)
Calculates PAYE and is used in macro
Macro input is Net Pay( E18) and calculates deductions in F13:F15 which can be checked against E15:E18
Hope this what is required.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thank you for this,
Although, is it possible for me to enter a specific Net pay figure I want to end up with in the spreadsheet, then the Macros calculates the Earnings and deductions automatically?
Enter Net Pay and macro calculates all the elements in column F.Please Login or Register to view this content.
Column E is the standard Gross-to-Net calculation so you can enter Basic Pay in E2 and it will calculate data in column E.
Last edited by JohnTopley; 01-13-2023 at 04:34 AM.
When I enter the Net Pay, all column F values are giving me zero
It means the macro could not determine what the Basic/Gross pay is to arrive at the desired Net Pay.
I have no idea how (or if) this is/can be resoved as there will always be many situations when the Net Pay is not valid subject the constraits of the deductions.
As an example: a Basic Pay of 9820 returns a Net Pay of 8999.75 so entering a Net Pay of 9000 is unlikely to return a valid answer. BUT entering a Net Pay of 8999.75 DOES return the correct result.
You will have to define limits on the "accuracy" of the calculated data: using rounding and increments of !/100 (penc in The UK) the macro will get close to a solution but maybe a pence out.
I will look further into the rouding issue.
Last edited by JohnTopley; 01-13-2023 at 06:50 AM.
Best estimate within 0.05 in F19Please Login or Register to view this content.
Last edited by JohnTopley; 01-13-2023 at 09:39 AM.
I see! Cause only certain Net Pays are possible...
Regardless, thanks for your help
See post #7
Wow!
I've just seen the post and looked at the spreadsheet you sent. Thank you for the solution!
See attached re my PM
You could use Goal Seek to find the solution:
Goal Seek.png
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks