Any Better solution to arrive at sales commission?

[Pavan.Sada.PS]

Dear Friends,

Please look at the attached file and advise if there is a better solution to arrive at the sales commission. my ranges in look up array of match and choose function are not dynamic. I was looking at the solution which should work dynamically for the addition values..

 

[NARAYANK991]

Hi Pavan ,

I would not call this a better solution ; the correct solution would use a lot of helper columns. However , for what it is worth try this multi-cell array formula :

=MMULT(SUMIFS($B$20:$B$1020,$C$20:$C$1020,$F$20:$F$29,$A$20:$A$1020,$B$4:$G$4)*($B$5:$G$5)*INDEX($B$6:$G$10,CHOOSE(MATCH(SUMIFS($B$20:$B$1020,$C$20:$C$1020,$F$20:$F$29,$A$20:$A$1020,$B$4:$G$4),{99,49,19,9,0},-1)+1,5,4,3,2,1),{1,2,3,4,5,6}),{1;1;1;1;1;1})

Select the range G20:G29 , and enter the above formula as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[Pavan.Sada.PS]

Hello Narayan Sir,

That's Great.. Short and simpler..

 

[prasaddn]

Hi Pavan ,


Select the range G20:G29 , and enter the above formula as an array formula , using CTRL SHIFT ENTER.

Narayan

Hi,

How does this mmult work? pls explain if you have time. I have seen number of times this function/formula being used particulary in this site itself.

also, Interestingly, if the formula is entered in one cell (even as CSE) the result is different compared to select the range G20:G29, why is this?

I understand we have given reference of salesman in F20:f29. And also unlike regular formula or regualar CSE formula here the formula remains exact same for all the cells in range G20:g29.

how does formula results differently ??

Regards,
Prasad DN

 

[Somendra Misra]

Hi Pavan,

Here's my attempt, not exactly a descent solution but single cell formula in G20 and than copy down.

=SUMPRODUCT(SUMIFS($B$20:$B$1020,$C$20:$C$1020,$F20,$A$20:$A$1020,TRANSPOSE(T(OFFSET($A$4,,MATCH(COLUMN($A:$F),COUNTIF($B$4:$G$4,"<"&$B$4:$G$4)+1,0)))))*N(OFFSET($B$5,,MATCH(TRANSPOSE(T(OFFSET($A$4,,MATCH(COLUMN($A:$F),COUNTIF($B$4:$G$4,"<"&$B$4:$G$4)+1,0)))),$B$4:$G$4,0)-1))*N(OFFSET($B$6,MATCH(SUMIFS($B$20:$B$1020,$C$20:$C$1020,$F20,$A$20:$A$1020,TRANSPOSE(T(OFFSET($A$4,,MATCH(COLUMN($A:$F),COUNTIF($B$4:$G$4,"<"&$B$4:$G$4)+1,0))))),{0,10,20,50,100})-1,{1;5;3;4;0;2})))

Confirm with Ctrl+Shift+Enter.

Regards,