Replace nested IF with other formulae...

Hi
Ive got one question... just for interest...

A friend of mine is Fiscal Agent. He works at state tax administration. He had this formula at work to calculate income tax.

Please Login or Register  to view this content.

I've reduced it. Now it looks like.

Please Login or Register  to view this content.

They're almost the same but I didn't use AND() function.

Now, my question is can this formula be replaced with SUMPRODUCT() or CHOOSE/MATCH ...I observed similar replacement but I don't remember details of that formulae.

Given the resulting formulae are not consistent for all bands (ie % changes, reduction of value pre * % not consistent, final addition etc) I would say no, not in this instance (IMO).

FWIW be aware that Sumproduct is not an efficient formula per se, it is "processed" in the same way as a "traditional" CSE Array and we know that we would generally try to avoid using Arrays if there are non-array alternatives (sometimes an Array is more succinct of course) ... you could possibly use CHOOSE but I don't really see how it would be more efficient than what you have already.

A Sumproduct appraoch is generally used as an alternative to an embedded IF approach where there is some level of consistency in a tiered system, say for example I wanted to calculate a commission value where I band the % according to sale price, eg:

First £500 @ 5%, Next 500 @ 10% and everything thereafter at 20%

So for a sale of £1600 I would get 500 @ 5%, 500 @ 10% and 600 @ 20% ... as an alternative to embedded IF I could use:

=SUMPRODUCT(--(saleprice>{0,500,1000}),saleprice-{0,500,1000},{0.05,0.05,0.1})

this approach is obviously useful when you have lots of tiers (note the % in the final inline array are incremental - ie 2nd rate 5% higher than 1st rate, 3rd rate 10% higher than 2nd rate)

Is that what you were referring to ?

If you want short try

=(K19>=75)*(K19*35%-MIN(2000,K19)*21%-(K19<=200)*10.5)

Thanx for explanation DonkeyOte. Very cognitively. I appreciate a lot.
Thanx for provided formula daddylonglegs. Cool formulae...