Apply IF statement formula with multiple conditions

[gtrstar]

=IF($C$5="Out of House",($C$14*0.5),IF($C$5="In House",($C$17*0.275))) is what I have now, I found out today that it is being changed so that if the commission is 6% vs. 5% and its an In House transaction, that the commision changes from .275 to .300 where cell C6 is the commission. So I need to take and make it do this

If the commision is 5% and its an in house transaction it would be the formula I have here however if the commission changes to 6% and its an In House transaction the $C$17*0.275 would change to $C$17*0.300. So far everything I have tried has just returned a false statement.

[jeffreybrown]

Try

Please Login or Register  to view this content.

[gtrstar]

That returnd #N/A

[icestationzbra]

Please Login or Register  to view this content.

@jefferybrown's formula may be spitting out #N/A because of 0.5 and 0.6, which should be 0.05 and 0.06 instead. also, from 5% to 6%, shouldn't the commission go up from 0.275 to 0.3, if i understand you correctly?

[gtrstar]

Yes that is correct 6% = .03 while 5% is = .0275. I'll give this a try thanks to each of you.

[gtrstar]

Ok that worked like I wanted it to. Now as I experiment I run into a whole new set of issues. Lets say the commission is 7% or 4% it returns #N/A because these values are not defined anywhere. So I am thinking how can I set this up to make it where any number >=.55 would be the .275 while any number <=.56 would be the .30?

[icestationzbra]

upload a workbook with sample data, we can help you set it up.

[gtrstar]

OK here it is and thanks in advance.

Ok so to recap the scenerio here

If the transaction is "Out of House" then the formula is Listing Side gets 60% and the selling Side gets 40% the agents involved then each get 50% of that, that is pretty straight forward

The problem comes into play when we have an In house transation where the commission is greater than 5% because the agent actually makes less money then if it was an Out of House transaction by 2.5%. So to fix this we are changing the policy to be one rate for transactions above 5% and one for transactions at or less than 5%. And due to competitive changes we are now offering fractions of percentages instead of the old singles of 5 6 or 7, so I need the system to be able to see a range vs. just a few static numbers.

I need this to calculate in Cell C20 the following.

If C6 is >5% and C5 is "In House" then the calculation would be C17 * 30% if C6 <= 5% then the calculation would be C17 * 27.5%

Any further clarification I'll be monitorings this board and reading old post as well most of the day.

[Ace_XL]

Maybe..

=CHOOSE(MATCH($C$5,{"out of house","in house"},0),$C$14*0.5,IF($C$6>0.05,$C$17*0.3,$C$17*0.275))

[gtrstar]

that worked perfectly thanks

[jeffreybrown]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save