One or the other

[JJ in SD]

Good Morning

I have a situation where a salesperson will either receive a “Minimum Commission” (G5) or under certain circumstances a Flat Commission (F5) but not both. I can’t figure out how to modify (Thanks Aladin), =IF(A5,IF(B5,MAX(25,B5*$A$2),0),0) (Cell G5) to return “0” if there is an “X” in Cell E5 or, if there is an amount in Cell F5 and not use the “X”.
Help please!

Thanks in advance,
JJ in SD

NRSG Pay 2003.xls
	A	B	C	D	E	F	G
1	$500
2	7%
3						Flat	Min
4	Gross	PGross	Fin	Flat		Comm	Comm
5	$764	$264	$1,159	$300	X	$90	$25.00
6
Sheet1

 

[flix]

nest another if statment springs to mind, not sure if this works but give it a shot:
=IF(E5="X",0,(IF(A5,IF(B5,MAX(25,B5*$A$2),0),0))

hope it works/helps

edit:
not 100% sure if you want the " " on the "X" part, but try it out

 

[JJ in SD]

Thanks Flix,

I copied the formula and pasted with and without the "" and it pasted as text.

JJ

NRSG Pay 2003.xls
	A	B	C	D	E	F	G
1	$500
2	7%
3						Flat	Min
4	Gross	PGross	Fin	Flat		Comm	Comm
5	$764	$264	$1,159	$300	X	$90	=IF(E5="X",0,(IF(A5,IF(B5,MAX(25,B5*$A$2),0),0))
6
7
Sheet1

 

[just_jon]

Perhaps --

=IF(F5,F5,IF(AND(A5,E5<>"X"),MAX(25,B5*$A$2),0))

 

[JJ in SD]

Thanks Jon,

Your formula mod works except it takes out the effect of =IF(A5,IF(B5,MAX(25,B5*$A$2),0),0). As you can see in Cell H10 there is $25. The result should be $0 as in Cell H11 or a commission as in H12. I put =IF(A5,IF(B5,MAX(25,B5*$A$2),0),0) In those 3 cells to illustrate the issue.

Thanks again,
JJ

NRSG Pay 2003.xls
	A	B	C	D	E	F	G	H
1		$500
2		7%
3							Flat	Min
4		Gross	PGross	Fin	Flat		Comm	Comm
5		$764	$264	$1,159	$300	X	$90	$0.00
6		($2,346)	($2,346)	$1,368			$0	$25.00
7		($2,700)	($2,700)	$100	$300	X	$90	$0.00
8		$2,915	$2,415	$1,980			$0	$169.05
9		$1,864	$1,864	$0			$0	$130.48
10			($500)					$25.00
11			($500)					$0.00
12		$1,250	$750					$52.50
Sheet1

 

[just_jon]

Is F always going to = "X" when G has a number? Does this work better?

=IF(F5="X",0,IF(B5*C5,MAX(25,C5*$B$2),0))

 

[JJ in SD]

Thanks Jon, That seems to do it.

Last Saturday you and RRDONUTZ replied to
http://www.mrexcel.com/board2/viewtopic.php?t=58503&highlight= For some resson I got a "Runtime Error" on your reply. RRDONUTZ said that you and he were pretty much the same on the answer so I used his =SUMPRODUCT(($D$12:$D$62=$D10)*$G$12:$G$62*$P$12:$P$62) from his post, the actual ranges in my application. Is there any way to add *N*12:*N*62 to that formula. Since I added the "Flat Commission" I need to include *N*12:*N*62 somehow. I tried and could not get it.

Thanks again in advance,
JJ

 

[just_jon]

Thanks Jon, That seems to do it.

Last Saturday you and RRDONUTZ replied to
http://www.mrexcel.com/board2/viewtopic.php?t=58503&highlight= For some resson I got a "Runtime Error" on your reply. RRDONUTZ said that you and he were pretty much the same on the answer so I used his =SUMPRODUCT(($D$12:$D$62=$D10)*$G$12:$G$62*$P$12:$P$62) from his post, the actual ranges in my application. Is there any way to add *N*12:*N*62 to that formula. Since I added the "Flat Commission" I need to include *N*12:*N*62 somehow. I tried and could not get it.

Thanks again in advance,
JJ

Sure; depending on what you want as criteria. If you want to proceed as above as long as the N-column value is, say, 0, then:

=SUMPRODUCT(($D$12:$D$62=$D10)*($N$12:$N$62=0)*$G$12:$G$62*$P$12:$P$62)

I'm sure there's a limit to the number of ranges SUMPRODUCT will handle, but I'm unaware of it. You do, however, have to keep all the ranges of equal size, otherwise the array falls apart. Has this answered your question? If not, let me know.

 

[JJ in SD]

Thanks Jon,

Let me work with it and I will post back.

JJ