Spilling Total using different ranges & If statement

[bearcub]

I am using the following formula to spill total commissions earned for our reps:

BYROW(I8:N86,LAMBDA(r,IF(Q8="Commission",SUM(r,K8:N8),J8)))

It works great except for paying someone a draw.

Column I are commissions earned based upon a data dump from our commissions software program. This column represents earning based upon new business for the month of April

Column J is a monthly draw paid to new hires. Our company policy states that the rep will be paid the larger of the draw or earnings amount. This rep should be paid 15K not 19K as shown in O11.

Note that I have a formula in column Q to allow either commission or draw to be diplayed. This is to let me know that a rep should be paid the draw amount and not the Commissions.

The formula "IF(Q8="Commission",SUM(r,K8:N8),J8)", without using ByRow & Lambda ,has worked up to now. When I us ByRow & Lambda the IF condition seems to be ignored -- probably because I am using Column J in the ByRow formula.

I substituted MAP for BYRow I get this. I just learning how to using the array functions so I realize that I don't totally understand how they are to be used.

What am I doing wrong.

Thank you for your help in advance.

 

[Fluff]

Will col J be blank if it's commission?

 

[bearcub]

Yes, J is only used for Draws (commissions never show up in this column, only draws).

f the rep had earned 16K instead of 4K then the draw would be ignored and the 16K would be paid. Reps are paid either the draw or earnings, which is larger.

 

[Fluff]

Ok, how about

=BYROW(CHOOSECOLS(I8:Q100,1,2,3,4,5,6,-1),LAMBDA(br,IF(TAKE(br,,-1)="draw",MAX(CHOOSECOLS(br,2),CHOOSECOLS(br,1)),SUM(br))))

 

[bearcub]

Works like a charm, thank you. How did you know that Choose columns would work instead of MAP

 

[Fluff]

With the map function all ranges need to be the same size.

 

[bearcub]

Oh, I see. Kinda like using the Index function where the arrays all have to be the same size.

 

[Fluff]

A slightly shorter version

=BYROW(CHOOSECOLS(I8:Q100,1,2,3,4,5,6,-1),LAMBDA(br,IF(TAKE(br,,-1)="draw",MAX(CHOOSECOLS(br,1,2)),SUM(br))))

Kinda like using the Index function where the arrays all have to be the same size.

With Index the arrays/ranges do not need to be the same size.

 

[bearcub]

What does the Take Function do in this formula? Looking at it, it looks like it is looking the first 2 columns of the array

 

[Fluff]

It's returning the last column in the array.