Consider a tax (e.g income tax) which is applied in the format:
So if a person earns 500€, tax is 0, if they earn 1500, then the first 500€ have no tax, the next 500€ have 12%, and the final 500€ have 28%.
What is the best way in Calc to determine the earnings by providing the salary.
I would avoid VLOOKUP or similar constructs.
Assume the limits are in A2:A4, and A6 has an unhrealistic “greater than any” value, say 1E300 (1 with 300 zeroes).
The rates are in B2:B4, and B6 contains the rate for amounts exceeding 1500.
Then =$F2*B$5-SUMPRODUCT(IF($F2>$A$2:$A$4;$A$2:$A$4;$F2);$B$3:$B$5-$B$2:$B$4) does the trick, and you wont need a longer formula if the table of rates is scaled up to any number of limits and associated rates.
The formula may look long or even complicated. In fact it is simple and efficient in spreadsheets because it doesn’t try to sequentialise the task.
See this attached example doument.
Excellent suggestion! It does indeed look clean and simple!
This looks very clever and nice, but it would be helpful if you could break this down into explanations of each (non-obvious) element, e.g.:
a) how it works to compare a value against a range inside the IF
b) how the “then” part of the IF is allowed to be a range
c) how you are allowed to subtract one range from another, and
d) why you are subtracting the sum of the product from $F2*B$5.
BTW I think it’s missing $ before that B$5.
(Slightly edited by @Lupp for better readability. Not yet studied or answered.)
With a vlookup function with an inner array it’s ease
=VLOOKUP(A3;{0|0;500|12;1000|28};2)
where cell A3 has the searched value, of course you can substitute the inner array {0|0;500|12;1000|28} with a range of cells with those values for search. Take care of use the proper separators as is in Menu/Tools/Options/LibreOffice calc/Formula.
Hello @mmalmeida
One of the possibilities is to use combination of VLOOKUP functions on the predefined Tax Table, as @mariosv proposed. I have created an example spreadsheet with all step by step explanation on how to use VLOOKUP to calculate progressive tax rates.
Thank you for the excellent suggestion - and for the detailed example - kudos for that!
I ended up accepting @Lupp’s suggestion as it seems simpler - although from what I gather the rationale is the same for both methods!
I also like @Lupp method better, cause it is not so obvious as VLOOKUPs, so it seems more interesting to me 
Voted for it as well.