excel commission report
 

In J5 I have the following formula which tells me what commission to pay.
=IF((D5<"")*(E5<""),LOOKUP(E5,{3,3.15,3.25,3.35, 3.45,3.55,3.65,3.75},{0.38,0.43,0.46,0.49,0.52,0.5 6,0.59,0.62})*D5-H5,"")
I would like to tie this formula to a specific product in a drop down menu
in B5 the products are Customer Renewal and RTCM. The othe products in the
menu need to be calculated like this E5-2.35*D5-H5
How can I change this formula in J5?

Thanks for all the help!!!

Try...

=IF((D5<"")*(E5<""),IF((B5="Customer
Renewal")+(B5="RTCM"),LOOKUP(E5,{3,3.15,3.25,3.35, 3.45,3.55,3.65,3.75},{0
..38,0.43,0.46,0.49,0.52,0.56,0.59,0.62})*D5-H5,E5-2.35*D5-H5),"")

Although, from the looks of it, you may want to place brackets like
this...

(E5-2.35)*D5-H5

So your formula would be...

=IF((D5<"")*(E5<""),IF((B5="Customer
Renewal")+(B5="RTCM"),LOOKUP(E5,{3,3.15,3.25,3.35, 3.45,3.55,3.65,3.75},{0
..38,0.43,0.46,0.49,0.52,0.56,0.59,0.62})*D5-H5,(E5-2.35)*D5-H5),"")

Hope this helps!

In article ,
"Dave O." wrote:

In J5 I have the following formula which tells me what commission to pay.
=IF((D5<"")*(E5<""),LOOKUP(E5,{3,3.15,3.25,3.35, 3.45,3.55,3.65,3.75},{0.38,0
.43,0.46,0.49,0.52,0.56,0.59,0.62})*D5-H5,"")
I would like to tie this formula to a specific product in a drop down menu
in B5 the products are Customer Renewal and RTCM. The othe products in the
menu need to be calculated like this E5-2.35*D5-H5
How can I change this formula in J5?

Thanks for all the help!!!