excel - Best method to calculate provision from a table with provision levels? -

i have table provision levels.

sales   provision 0       5% 20 000  22% 100 000 30% 

a salesman has 5% provision on first 20 000, 22% on next 80 000 , 30% on above that.

for example salesman sells 230 000 have provision

=20 000 * 0,05 + 80 000 * 0,22 + 130 000 * 0,30 

how can express efficiently formula? formula

• needs easy copy several rows (where salesman described each row)
• needs work if add more provision levels

well can works (hopefully) requirement #2:

needs work if add more provision levels

but note unsure requirement #1:

needs easy copy several rows (where salesman described each row)

anyway, data:

       b      c           d          e ------------------------------------------ 0       0.05        value           230000 20000   0.22        total provision  57600 100000  0.3  

i used formula in e2:

=iferror(sumproduct((indirect("a2:a"&match(e1,a:a,1))-indirect("a1:a"&match(e1,a:a,1)-1))*indirect("b1:b"&match(e1,a:a,1)-1))+(e1-index(a:a,match(e1,a:a,1)))*index(b:b,match(e1,a:a,1)),e1*b1) 

if break formula down:

the first basic step find index @ sale value resides. first lower value compared sale value. in example data straight forward because index last in provision list. accommodate sale values fall within list range can use:

match(e1,a:a,1) 

where e1 sale value , a:a sale-provision list.

using can incorporate indirect desired range need work with. in case a1 & index:

indirect("a1:a"&match(e1,a:a,1)) 

but within range need figure out 2 distinct values:

• the provision value index lower our sale value (i.e. 20 000 * 0.05 + 80 000 * 0.22)
• the rest of provision sale value (i.e. 130 000 * 0.30)

so first value need set array this:

(20000   -     0) * 0.05  =  20000*.05 =  1000 (100000  - 20000) * 0.22  =  80000*.22 = 17600                                    sum = 18600 

that can done using

(a2:a3 - a1:a2)*(b1:b2) 

but put in our indirect formula, like

indirect("a2:a"&match(e1,a:a,1))     <- a2:a3 indirect("a1:a"&match(e1,a:a,1)-1)   <- a1:a2 indirect("b1:b"&match(e1,a:a,1)-1)   <- b1:b2  (indirect("a2:a"&match(e1,a:a,1))-indirect("a1:a"&match(e1,a:a,1)-1))*indirect("b1:b"&match(e1,a:a,1)-1)) 

just surround sumproduct total:

sumproduct((indirect("a2:a"&match(e1,a:a,1))-indirect("a1:a"&match(e1,a:a,1)-1))*indirect("b1:b"&match(e1,a:a,1)-1)) 

then second value total sale value subtracted our index value , multiplied corresponding provision rate. be:

(e1-a3)*b3 

we don't need indirect here, couple index - match lookups do:

e1                          <- e1 index(a:a,match(e1,a:a,1))  <- a3 index(b:b,match(e1,a:a,1))  <- b3  (e1-index(a:a,match(e1,a:a,1)))*index(b:b,match(e1,a:a,1)) 

then adding formulas results in derived formula showed earlier.

the final addition add iferror wrapper because if value less first non-zero provision index-match , indirect fail. in case of error means need multiply sale value first provision rate:

e1*b1 

and of course if add additional provision row:

       b      c          d          e ------------------------------------------ 0       0.05        value           230000 20000   0.22        total provision  63600 100000  0.30 200000  0.50 

or change provision:

       b      c          d          e ------------------------------------------ 0       0.05        value            22000 20000   0.22        total provision   1440 100000  0.30 

the formula index proper provision , calculate properly.

also,

since know use commas , decimals in locale , realized don't here formula semi-colon list separator:

=iferror(sumproduct((indirect("a2:a"&match(e1;a:a;1))-indirect("a1:a"&match(e1;a:a;1)-1))*indirect("b1:b"&match(e1;a:a;1)-1))+(e1-index(a:a;match(e1;a:a;1)))*index(b:b;match(e1;a:a;1));e1*b1)