Reducing list of values by fixed amount until zero

Hi,

A fixed penalty of $16.78 is charged for every $50 in outstanding debt balances.
E.g. If I want to clear a debt balance of $112 then I should be paying $16.78*3 in penalties.

What formula can I use if I have a long list of different amounts of debt balances?
I know I can use a long IF formula but i'm looking for a more compact, cleaner formula.

Thanks in advance!

Something like that?

=ROUNDUP(112/50,0)*16.78

Hi ChemitB,

Sorry for the oversight. Here it is.

If you round up the amounts then please check the formula for total amount:

=+ROUNDUP(A2/50,0)*16.78

And this one for the number of charges:
=ROUNDUP(A2/50,0)


Hope this is what you meant...

Worked like a charm! Thank you very much.

If instead of a fixed charge, the charge was tiered based on the outstanding amount, how would the formula look?
For example, first $50 incurs penalty of $16.78, second $50 incurs $25, etc.

Hi,
I used sumproduct/lookup array formula for this calculation.

=SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&B2)),{1,2,3,4;16.78,25,30,32}))

For the example I took the below tiered rates for penalties, you can change it within the formula if needed:
16.78
25
30
32

Belinda200,

This is great. Again, many thanks.

If you could just indulge me one final time:
If the charge goes back to being a fixed charge i.e. $16.78, but we change the range from being fixed at $50 to a tiered basis.
E.g. first $50 incurs penalty of $16.78, second $30 incurs $16.78, third $20 incurs $16.78.

you upgrade the difficulty of your questions.......I feel like I'm in a test
Well I have to think about it, maybe other memebers will know how to solve this faster than me.

lol. I was more of the opinion this last one was a downgrade. Thank you though. I hope someone else has the solution.

OK, so I changed the # of charges according to the set of rules, but no instruction what is the rate charge for the remaining Amount surpasses $100
so I left it empty...

=IF(A2<=50,1,IF(AND(A2>50,A2<80),2,IF(AND(A2>=80,A2<=100),3,"")))

The result is multiplied by 16.78

Oh you did it! Youre the best! Arigatou gozaimasu!

you're welcome.

If your question is resolved, please mark it SOLVED using the thread tools