Formula for Case Rate Payment prioritization

Hi all,

I need help with a formula that assigns a hierarchy to reimbursement based on payment type and contract rate. In particular, if the payment type is "Case" then we only get reimbursed for the line with the highest case rate; additionally, if there are 2 or more lines with the highest case rate then we only get paid on one of them (it doesn't matter which one necessarily, only the amount matters).

I have attached a spreadsheet with examples of how the formula should work, so if anyone has any ideas please let me know.

Thanks!

Attached is a more inclusive workbook with all of the rules I need to apply to the reimbursement column.

Fantastic! Seems to be working like a charm, thanks for your help.

Actually I found one slight miscalculation that I have been trying to solve but I can't seem to figure it out.

Only the highest case rate applies even if it is smaller than say a Visit line.

So I need the below formula to look for the max "Case", not the max of the whole row if that makes sense.

IF([@[Payment Type]]="Case",IF(ROWS(F$16:F17)=MATCH(MAX([Rate]),[Rate],0),[@Rate],0)

I put Visit and Fee Schedule into the formula because those are the two other possible payment types.

The payment type possibilities are:

• Add-on
• Case
• Fee Schedule
• Visit
• POC

Add-on always gets paid, only the highest case rate applies, if case rate is in the payment type column then POC Visit and Fee Schedule are 0, but if case does not exist then those get paid at [Rate].

I believe that covers everything, but I will update if I uncover any other rules.

The MAXIFS seems like it would solve the issue but I don't think that function is not available in my Excel, which is odd because I am operating on Windows 10 using Excel 2016.

Wonderful, I put it into my main workbook and it works!

Thank you again for you effort.