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.

 

[bearcub]

Is that the purpose of using -1 in the ChooseCol & Take functions?

How does the -1 do its magic in the formula:

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))))

I noticed where the -1 is placed but I don't know how it works. What would be returned if the -1 was replaced with -2 or -3?

The formula is magic but I don't understand how it works to create its "magic".

 

[Fluff]

Try looking at explanations of how the functions work. There are plenty of sites with detailed info.

 

[bearcub]

Will do.