Is there a better way to write the following formula?

[KiyomiNox]

=IF(OR($B$11=$B$12,$B$12=0),IF($H$10="INTERNET",IF(($H$46*0.25)<300,IF(($H$46*0.25)=0,0,300),($H$46*0.25)),IF(($H$46*0.25)<200,IF(($H$46*0.25)=0,0,200),($H$46*0.25))),(IF($H$10="INTERNET",IF(($H$46*0.25)<300,IF(($H$46*0.25)=0,0,300),($H$46*0.25)),IF(($H$46*0.25)<200,IF(($H$46*0.25)=0,0,200),($H$46*0.25))))/2)

So essentially I use this to determine how much commission someone is supposed to make. Everyone always makes at least $200, but that gets upped to $300 if the sale came from the internet. H46 is the profit of the deal. H10 is the type of deal but "Internet" is the only option that matters. Commission is 25%. B11 & B12 are the sales people because there are occasionally 2 of them so the commission has to be split reducing the minimum to $100 & $150 respectively.

If anyone has any ideas of how to make this a little nicer, I would greatly appreciate it

Thanks in advance to everyone who helps!

 

[KiyomiNox]

I also forgot to mention that I have to add that if B11 or B12 (the sales advisor) is set to "House" then the commission is 0.

 

[mopp1]

=($B$11<>"House")*($B$12<>"House")*MAX(200+($H$10="Internet")*100,$H$46*0.25)/IF(OR($B$11=$B$12,$B$12=""),1,2)

 

[Eric W]

Similar in concept to Mopp1's:

=IF(OR($H$46=0,COUNTIF($B$11:$B$12,"House")),0,MAX($H$46*0.25,IF($H$10="INTERNET",300,200)))/IF(OR($B$11=$B$12,$B$12=0),1,2)

But a value of 0 in H46 will result in a 0 commission.

 

[KiyomiNox]

=($B$11<>"House")*($B$12<>"House")*MAX(200+($H$10="Internet")*100,$H$46*0.25)/IF(OR($B$11=$B$12,$B$12=""),1,2)

This is fantastic! Thank you so much! Is there a way I can modify this for the cell that contains the commission for person 2 where it would be 0 if B12=B11? Currently I have:

=IF(OR($B$11=$B$12,$B$12=0),0,(IF($H$10="INTERNET",IF(($H$46*0.25)<300,IF(($H$46*0.25)=0,0,300),($H$46*0.25)),IF(($H$46*0.25)<200,IF(($H$46*0.25)=0,0,200),($H$46*0.25))))/2)

in that cell which is similar to the original formula.

 

[KiyomiNox]

Ok, so I ended up going with

=($B$11<>"House")*MAX(200+($H$10="Internet")*100,$H$46*0.25)/IF(OR($B$11=$B$12,$B$12=""),1,2)

for commission 1 and

=($B$12<>"House")*MAX(200+($H$10="Internet")*100,$H$46*0.25)/IF(OR($B$11=$B$12,$B$12=""),1,2)*($B$11<>$B$12)

for commission 2. Thoughts?

 

[mopp1]

I think you may want to add *($B$12<>"") to your commission 2 formula. That way if B12 is blank then commission 2 will be zero rather than the full commission (which is what it looks like now it would calculate).