IF Function for Scale

[Harlequin]

Hello.

I have 3 columns , 1 with name, 1 with amount & 1 with a rate.

I am looking for a formula in Column D that says

If Name = "Tony" then multiply 0-,20,000 by 5%, 20,001-40,000 by 8%, & whatever is left of the balance (.ie 50,000-40,001) by 3.5%, else just Col B*Col C




Name - Amount - Rate


James - 10,000 - 5%
David - 15,000 - 5%
Tony - 50,000 - 5%
Mike - 20,000 - 5%
John - 75,000 - 5%


Any help would be much appreciated.

[arthurbr]

Hi
what is the relation between the names and the percentages ?
In your example there are names with an amount and all 5% ?

Maybe adding a sample spreadsheet would help

[Harlequin]

Hi

The % relates to a interest charge on amounts owed by those names. The % (in this example for simplicity is a flat 5%).

However as my report is only shows the total amount for each name, I would like a formula that can scale that total amount. ie If Name = "Tony" then multiply 0-,20,000 by 5%, 20,001-40,000 by 8%, & whatever is left of the balance (.ie 50,000-40,001) by 3.5%, else just Col B*Col C

[arthurbr]

For Tony you can use following formula
=SUMPRODUCT(--(A1>{0;20000;40000});(A1-{0;20000;40000});{0,05;0,03;-0,045})

The last array is one with incremental rates, meaning the difference of each rate and the previous rate (5% stays, next one is 8%-5%, and 3,5%-8%)