Conditional Multiplication in Excel

  • Please help. I am trying to create formulas that will calculate commission based on a set of rules: as detailed in the attached worksheet.


    - Some transaction types calculate commission as a percentage of value (Airtime Top-up, bill payments, Electricity tokens and pay-tv)


    - Some transactions earn a fixed amount of commission - depending on the value of the transactions ( e.g - 1-250 = 3 commission, 251-500 = 4 commission )


    I now need to be recording these transactions on a worksheet . I am trying to create a formulae in the cell - such that if i enter 400 in cell a A 20, Cell B20 should automatically calculate the 4.00 commission on that transaction.


    If i enter 1000.00 in A21, i want B21 to automatically calculate the commission of 5.00 .


    I know that for B20 and B21 to calculate correct commissions, the conditions needs to be defined in the formulae.


    Hoping i am clear and making sense.


    See the attachment

  • Try this in B20


    =IF(A20>2000,5.5,IF(A20>500,5,IF(A20>250,4,3)))


    copied down.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!