Need help with an if/then - either/or formula

[Rampton]

I'm trying to create a spreadsheet that will keep track of my sales reps' advance/draw vs their revenues and commission amounts. For example: I have a sales rep who takes home either the greater of $3,000/mo, or 70% of revenues, provided his draw balance is at $-0-. So, he will always make at least $3,000/mo, but if he only sells enough that his commission at 70% would have been $2,000, then he earns the $2,000 and I 'advance' $1,000 so that he takes home $3,000. But, I don't know how to create a formula that looks at either the greater of 70% of revenues or $3,000 - but only when the draw balance is $-0-. Help please?

 

[MB110]

I tried to reply with a formula but it won't post completely. For some strange reason it gets cut short when I preview it. Let me state it in words.

See Post Below

 

[mikerickson]

@MB110, if you put a space before and after each < , it should post correctly.

 

[MB110]

Thanks mikerickerson

A2= Sales Revenue
B2 = Draw Balance
C2 = Actual Pay (Greater of $3000 or 70% of Sales Revenue)

=IF(AND(B2=0,A2*0.7 < 3000),(3000-(A2*0.7))+A2*0.7,A2*0.7)

Only works if draw balance is zero. You will need to define what to do if not equal to zero.

hth,
mb110

 

[MB110]

Correction

=IF(AND(B2=0,(A2*0.7) < 3000),(A2*0.7)+3000-(A2*0.7),A2*0.7)