Multiple Relationship formula involving tiered levels and calculations based on selections

I have been trying to solve this for the past 2 days without any success. There are many criteria for this formula so I hope I communicate this effectively.

I am trying to calculate total payout (G31) which ultimately is the sum of each "Loan Payout" listed in column G in the attachment. There are many things that factor into the calculation for each cell in column G.

If the total loan amounts sum (F31) is between 1 - 400,000 then each individual loan amount in column F is multiplied by .003 and the answer recorded in the next cell to the right in Column G.

If the total loan amounts sum (F31) is between 400,001 - 700,000 then each individual loan amount in column F is multiplied by .004. and the answer recorded in the next cell to the right in Column G.

If the total loan amounts sum (F31) is between 700,001 - 1,000,000 then each individual loan amount in column F is multiplied by .005 and the answer recorded in the next cell to the right in Column G.

If the total loan amounts sum (F31) is between 1,000,001 - 1,500,000 then each individual loan amount in column F is multiplied by .006 and the answer recorded in the next cell to the right in Column G.

If the total loan amounts sum (F31) is between 1,500,001 and up then each individual loan amount in column F is multiplied by .0065 and the answer recorded in the next cell to the right in Column G.

There are three other factors to consider that figure into calculating G31 correctly. Any loan with a loan number listed in Column A is considered an active loan. If a loan is NOT an active loan (No Loan Number is given) then the green check boxes in columns C, D, and E should not be factored into the calculation. It should be this way in the event that a green check box is inadvertently checked yes and there isn't a loan number, otherwise it will miscalculate the total payout (G31) amount.

The way the green boxes these boxes are used for the calculation are as follows:

For any active loans, if 80% or more of the "File Docs" boxes (column D) are checked yes then .001 is added to the calculation of each of the above tiers. If less than 80% are checked then there is nothing added to the tiers. When a box is checked yes a true statement is given in the same row in column J.

For any active loans, if 100% of the "HMDA/3 Day Print" boxes (column E) are checked then .0003 is added to the calculation of each of the above tiers. If less than 100% are checked then there is nothing added to the tiers. When a box is checked yes a true statement is given in the same row in column K.

For any active loans, that has the Employee Loan box checked the loan payout (Column G) for that loan is capped at $250. When a box is checked yes a true statement is given in the same row in column L.

As an example for all of this working together for 8 active loans:

Loan Amount total: $1,725,500 (.0065 received)
7 out 8 loans had yes checked for File Docs (.001 received)
2 out 3 loans had yes checked for HMDA/3 Day Print (nothing received because it was less than 100%)

The total payout would be .0075 * each of the loan amounts and then the sum of those totals. If any of the loans were employee loans then they would be capped at $250 per loan.

Here is the Excel file. Pipeline Payout Calculator Example.xlsxExcel.JPG

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

After that an examle workbook would be much better than picture since we can't work on the picture.
Make sure to remove all private informations.

It's hard to follow it.
This is what I come up to now so maybe it helps you to continue.

Still didn't calculated caped to 250$ because Employers checkboxes doesn't reference anywhere.
When you fix ti just use =IF(reference_cell = TRUE, MAX(250, formula), formula)

I put another column not to mess your table but you can copy it in G1.
Result is OK, just need one 0 more (marked as red):

Formula:

Please Login or Register  to view this content.

This returns desired result.

Except for employee loan.
What else need to be done?
Can you give example.

Here, try it: