Notifications
Clear all
General Excel Questions & Answers
2
Posts
2
Users
0
Reactions
72
Views
Topic starter
Hi, can anybody give me the if statement for the following, please
Calculating UK employer's nic (15.05% from next year) from a list of gross salaries, to show zero when no er nic applies (because under the limit for ER NIC) and to show nic payable in all other lines.
| Staff Type | Employee name | Job -Title | Full or PT (%) | Salary 1 as at 1st April 2021 | Months | Salary 2 after in yr increment | Months budgeted | Additional Allowances | NEST = 3% Gross | No. of times employee split across CC | Pro-rata salary | ER NI | Pension | Total draft budget | Volunteering costs | Jan - Mar 2022 | February | March | Monthly |
| 2022-23 Projections | Yr / Pro-rata | ||||||||||||||||||
| Permanent | xx | Education Co-ordinator | 0.8000 | 22,221.00 | 12.00 | - | 3% | 1 | 17,776.80 | 533.30 | 18,310.10 | 1,525.84 | |||||||
| Sessional | xx | Farmyard Support Worker | 0.4000 | 22,221.00 | 12.00 | - | 3% | 1 | 8,888.40 | 266.65 | 9,155.05 | 762.92 | |||||||
| Permanent | xx | Coriander Club Co-ordinator | 0.8000 | 22,221.00 | 12.00 | - | 3% | 1 | 17,776.80 | 533.30 | 18,310.10 | 1,525.84 | |||||||
| Permanent | xx | Site Development Manager | 0.6000 | 25,000.00 | 12.00 | 3% | 1 | 15,000.00 | 450.00 | 15,450.00 | 1,287.50 | ||||||||
| Permanent | xx | Farmyard Co-ordinator | 1.0000 | 22,222.00 | 12.00 | 3% | 1 | 22,222.00 | 666.66 | 22,888.66 | 1,907.39 | ||||||||
| Permanent | xx | Project Officer | 0.4000 | 25,000.00 | 12.00 | 3% | 1 | 10,000.00 | 300.00 | 10,300.00 | 858.33 | ||||||||
| Permanent | xx | CEO | 0.8000 | 40,000.00 | 12.00 | 3% | 1 | 32,000.00 | 960.00 | 32,960.00 | 2,746.67 | ||||||||
| Permanent | xx | Site Development Manager | 0.6000 | 25,000.00 | 12.00 | 3% | 1 | 15,000.00 | 450.00 | 15,450.00 | 1,287.50 | ||||||||
| Permanent | xx | Finance & IT Manager | 0.4000 | 32,500.00 | 12.00 | 3% | 1 | 13,000.00 | 390.00 | 13,390.00 | 1,115.83 | ||||||||
| Kick-start | xx | Finance | 0.4000 | 22,221.00 | 12.00 | 3% | 1 | 8,888.40 | 266.65 | 9,155.05 | 762.92 | ||||||||
| Sessional | xx | Cleaner (4 mornings x 2hrs) | 0.2133 | 20,962.50 | 12.00 | 3% | 1 | 4,472.00 | 134.16 | 4,606.16 | 383.85 | ||||||||
| Sessional | xx | Cleaner (2 mornings x 2hrs) | 0.1067 | 20,962.50 | 12.00 | 3% | 1 | 2,236.00 | 67.08 | 2,303.08 | 191.92 | ||||||||
| Sessional | xx | Sessional Staff | 0.3139 | 22,951.50 | 12.00 | 3% | 1 | 7,203.71 | 216.11 | 7,419.82 | 618.32 | ||||||||
| Holiday Pay* | 0.1270 | 22,221.00 | 12.00 | 3% | 1 | 1,709.38 | 51.28 | 1,760.67 | |||||||||||
| Sessional | Sessional Staff (11.50hr) | 0.0000 | 22,425.00 | 12.00 | 3% | 1 | - | - | - | - | |||||||||
| 14,974.84 | |||||||||||||||||||
| * 4 WEEKS HOLIDAY PAY BUDGETED FOR J BETTENSON | |||||||||||||||||||
| *** enter if statement to produce zero not minus in col N where no ER Nic payable. | 181,458.70 | ||||||||||||||||||
| 15,121.56 | |||||||||||||||||||
Posted : 14/11/2021 2:19 am
Hello,
Welcome to MOTH.
If you upload a sample file I am pretty sure someone can help you out. Would also be good if you in the sample file also show what the expected result should be.
Br,
Anders
Posted : 14/11/2021 6:31 pm
