Excel spreadsheet for calculating tiered charges
Hi - I'm trying to work out how to create a spreadsheet which works out the platform charge for a given value, and also shows the amount charged within each tier. We use Zurich a lot and they have tiered charges, and I have to show in the report the amount that is charged at each tier. Obviously I can do it manually with a calculator but a spreadsheet would make life easier and quicker, and reduce the possibility of errors. I tried using =SUMPRODUCT but I don't really understand how it works and I'm tying myself up in knots - I don't really want to use loads of nested =IF arguments as this would probably overcomplicate things. It gets complicated in the higher tiers, especially where we will have a range of values for different clients so some of the tiers will not be needed.
Alternatively, if anyone has such a spreadsheet would you be willing to share?
I hope this makes sense but happy to explain further if needed