Help with 401K-Roth Match formula for excel template

Hello all,

I am trying to create an excel spreadsheet with a formula for a 401K/Roth match that we can use to 1) double check current employee's match, and 2) use for new employee's. the match is as follows:

Employer match should be calculated based on the total contribution % of traditional 401k pre-tax contribution and Roth contribution. The match should be 50% of the first 5% of the “total” employee contribution.
If an employee contributes 1% to 81 (401) and 10% on RTP (Roth), the match should be .5% on 81 and 2% on RTP.
If the employee contributes 10% to 81 (401) and 10% to RTP (Roth), the match should be 2.5% on the 81 and 0% on the RTP.

The current template we have calculates based off the employee's eligible wages, so not sure that is correct and I am getting caught up on how to show the breakdown of 401 versus Roth and map that out in my spreadsheet, how to include elected dollar amounts in the formulas and not just percentages. I have few example employee's where the match was missed, so i need to figure out what it should be, and a few example employee's who had a match, and we want to make sure the system is calculation the match correctly.

Any guidance is truly appreciated!