Hopefully Simple If with max and minimum values - TIA

[morsecode]

Hey guys, first of all thanks in advance for your help. I am an average Excel user and looking forward to learning more here in these forums. This is my first post so please be easy on me.

I am a real estate appraiser and I pay a contract employee to help me out. I keep an excel spreadsheet in a shared dropbox folder that we both can see. I have a column where I put how much I am getting paid for a job and I have a column that has how much I am paying the contractor for it. I would like this field to calculate automatically with the following rules. I am paying him 2/3 of the total cost of the job with a minimum payment of $200. If the job is paying me under $200, I am paying the contractor the whole amount. So if a job is paying me $600, the formula should return $400 for his field. If the job is paying me say $240 then it should return $200 for him due to the $200 minimum. And finally if the job is paying $125, it should return $125 as that is the total amount of the job and it is below $200.

Hopefully that is clear enough. Thanks again for your assistance!

[Jonmo1]

Try

=MAX(MIN(200,A2),A2*2/3)

[etaf]

=if( cell with amount <=200, cell with amount, MAX(cell with amount*2/3,200) )

[morsecode]

Thanks so much Jonmn1. We are almost there. That formula calculates everything great but all of my lines that havent been filled in yet so $200. Is there something we can add to the formula so that if the job amount is 0 it returns a blank? Thanks!

Oh, and also, I want it rounded to the nearest dollar. No need geting involved with change. Thanks!

[Jonmo1]

Try
=ROUND(MAX(MIN(200,A2+0),A2*2/3),0)

[Jonmo1]

Although, dealing with paying contractors....
They'd probably prefer you to round UP..

=CEILING(MAX(MIN(200,A2+0),A2*2/3),1)


Or if the rounding should benefit you instead of the contractor, change Ceiling to Floor

[morsecode]

Beautiful! Thanks so very much!