Trouble Creating A Multi-Nested Conditional

[jjonesmii]

Hi...

Can You Help? I'm trying to create an excel conditional statement for the following variable commission calculation:

If C1="Y" AND the price is over 100K, then use formula A (Formula below) OR If C1="Y" AND the price is under 100K, then use formula B. Otherwise If C1 ≠ "Y" AND the price is over 100K, then use formula C OR If C1≠"Y", then use formula D.

Formulae C & D have worked using this:

=If(C2<=100000,C2*0.035,(sum(100000*3.5%)+((C2-100000)*1.5%)))

Formulae A & B Have worked using this:

=IF(D8<=100000,SUM((D8*0.035)*60%),(SUM(100000*3.5%)+((D8-100000)*1.5%)*60%))

Here's the Conditional I'm Trying To Use Now. It works for some of my figures, but not all.

=IF(AND(E8="Y"),IF(D8<=100000,((D8*0.035)*60%),(SUM(100000*3.5%)+((D8-100000)*1.5%)*60%)),IF(D8<=100000,D8*0.035,(SUM(100000*3.5%)+((D8-100000)*1.5%))))

Thoughts?

[Bob Phillips]

is this what you want?

=IF(E9="Y",IF(D9<=100000,D9*0.035*60%,S100001*3.5%+(D9-100000)*1.5%*60%),
IF(D9<=100000,D9*0.035,100000*3.5%+((D9-100000)*1.5%)))

[jjonesmii]

Hi Bob...

Thanks for trying to help. Unfortunately, it produces roughly the same results I produced previously. Your equation is much cleaner than mine tho.

If you're still willing to help, here's some context to help perhaps make my goal clearer.


If my staff sells a home for under $100K, and the client is there's, then they receive a commission of 3.5% of whatever amount the home sold for under $100K.
Ex--> $100,000 home provides my staff with $3500 in commission.


If my staff sells a different home for the same price, but they're helping one of my clients, then they receive 60% of the regular commission.
Ex--> $100,000 = $2100 commission.


If my staff sells a home for over $100K, and the client is there's, then they receive a commission of 3.5% on the first $100K and 1.5% on anything thereafter.
Ex--> $250,000 = ($100,000*3.5%)+($150,000*1.5%)=$5750


If my staff sells a different home for the same price, but they're helping one of my clients, then they receive 60% of the regular commission.
Ex--> $250,000 = [($100,000*3.5%)+($150,000*1.5%)*60%]=$3450



I want to differentiate between the amount of their commission if the home was sold to my client or theirs.

Here's the file of what I'm trying to do, what appears to be happening, and what things "should" look like.

[FDibbins]

jjonesmii I have removed your fancy formatting from your posts - it is unnecessary and just wastes space.

Also, it looks like your file - and your description - are out of sync? Your example formula references col C, but C is empty in your file?

[FDibbins]

OK try this...
=IF(D8 < 100000,D8*0.035,(D8-100000)*0.015+3500)*IF(E8="y",0.6,1)

[FDibbins]

I guess that answered your question thanks for the feedback