SOLVED

Please help with Excel Formula for applying late fees

%3CLINGO-SUB%20id%3D%22lingo-sub-2423458%22%20slang%3D%22en-US%22%3EPlease%20help%20with%20Excel%20Formula%20for%20applying%20late%20fees%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2423458%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20balance%20is%20less%20than%20%24200%20then%205%25%20of%20the%20balance%3C%2FP%3E%3CP%3EIf%20balance%20is%20between%20%24200-%241000%20then%20total%20fee%20charged%20%2410%3C%2FP%3E%3CP%3E%2410.00%20per%20month%20for%20the%20first%20%241%2C000.00%20plus%205%25%20percent%20for%20any%20amount%20in%20excess%20of%20%241%2C000.00%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2423458%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2423474%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%20with%20Excel%20Formula%20for%20applying%20late%20fees%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2423474%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1073147%22%20target%3D%22_blank%22%3E%40Hemant_Sharma1615%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20balance%20is%20in%20cell%20B2.%20The%20formula%20for%20the%20fee%20is%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(B2%26lt%3B%3D200%2C%205%25*B2%2C%2010%2B5%25*MAX(B2-1000%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2423479%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%20with%20Excel%20Formula%20for%20applying%20late%20fees%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2423479%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20was%20again%20faster%20than%20me%20%3CLI-EMOJI%20id%3D%22lia_anguished-face%22%20title%3D%22%3Aanguished_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3Egreat%20solution%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
New Contributor

If balance is less than $200 then 5% of the balance

If balance is between $200-$1000 then total fee charged $10

$10.00 per month for the first $1,000.00 plus 5% percent for any amount in excess of $1,000.00

3 Replies
best response confirmed by Hemant_Sharma1615 (New Contributor)
Solution

@Hemant_Sharma1615 

Let's say the balance is in cell B2. The formula for the fee is:

 

=IF(B2<=200, 5%*B2, 10+5%*MAX(B2-1000,0))

@Hans Vogelaar was again faster than me
great solution
Thank You so much Hans Vogelaar, I really appreciate it.