Need IF formula for a commission-tracking spreadsheet in excel

[RealEstate135]

I would like to set up an IF statement in excel and need some assistance.

Scenario: I am tracking commissions between two real estate agents. If one of them was the primary seller, they get 60% of the commission. The other agent gets 40%.

What I want to do:
If "A" (first initial of agent's name) is entered into F2, and the sum of 800 is entered into G2, I would like 2.5% of 800 to be applied into J2.
60% of the 2.5% to be applied into K2 and 40% of the 2.5% to automatically go into L2.

How would I go about building this formula?

 

[dreid1011]

What's the significance of the first initial being entered? Also, does this have to be a single formula? If so, would a macro or VBA be ok? And, is the 60% agent always in the same column?

 

[RealEstate135]

The letter "A" is the first letter of the agent's name.
Ideally would like to be one formula but ok if not.
For the last question, no - if it is agent "A", it would be 60% in K2 and 40% in L2. If it is agent "B", it would be 60% in L2 and 40% in K2.
Let me know if this doesn't make sense.

 

[dreid1011]

Ok, that's fine. If you're not opposed to a macro, I will do that, otherwise, I am not too sure I can come up with a single formula to manipulate multiple cells. But I will show you what I do both ways.


Ok, so here is what I have for formulas:

Cell F2 = Initial
Cell G2 = Amount
Cell H2 = 2.5% of Amount. =G2*0.025
Cell I2 = 60% if Agent A, 40% if Agent B. =IF(F2="A",H2*0.6,H2*0.4)
Cell J2 = 40% if Agent A, 60% if Agent B. =IF(F2="A",H2*0.4,H2*0.6)

Then you can just copy these formulas down their respective columns for as many rows as you need. Also, are there only two agents? If so, the formulas can stay as is, but if there are more, they will need to be adjusted a little.

 

[zico8]

formula in J2:
=G2*0,025

formula in K2:
=IF(F2="A";J2*0,6;IF(F2="";0;J2*0,4))

formula in L2:
=IF(F2="B";J2*0,6;IF(F2="";0;J2*0,4))

 

[dreid1011]

Ok, that's fine. If you're not opposed to a macro, I will do that, otherwise, I am not too sure I can come up with a single formula to manipulate multiple cells. But I will show you what I do both ways.


Ok, so here is what I have for formulas:

Cell F2 = Initial
Cell G2 = Amount
Cell H2 = 2.5% of Amount. =G2*0.025
Cell K2 = 60% if Agent A, 40% if Agent B. =IF(F2="A",H2*0.6,H2*0.4)
Cell L2 = 40% if Agent A, 60% if Agent B. =IF(F2="A",H2*0.4,H2*0.6)

Then you can just copy these formulas down their respective columns for as many rows as you need. Also, are there only two agents? If so, the formulas can stay as is, but if there are more, they will need to be adjusted a little.

Oops, made a mistake in my reply. Fixed it

 

[RealEstate135]

Oops, made a mistake in my reply. Fixed it

Thanks for this - I made a few tweaks and have a (I hope) simple question, how do I combine two IF statements in one cell?

Cell F32 = Initial
Cell G32 = Amount
Cell J32 = 2.5% of Amount. =G32*0.025
Cell K32 = 60% if Agent A, 40% if Agent B. =IF(F32="A",J32*0.6)=IF(F32="F",J32*0.4)
Cell L32 = 40% if Agent A, 60% if Agent B. =IF(F32="B",J32*0.4)=IF(F32="F",J32*0.6)

Should there be a ; instead of = in front of the second IF statement:
=IF(F32="A",J32*0.6)=IF(F32="F",J32*0.4)

 

[dreid1011]

You can do something like this:
=IF(A1>=100,Yes,IF(A1<=0,No,""))


Put the 2nd IF statement in the ELSE position of the preceding IF statement.

 

[RealEstate135]

You can do something like this:
=IF(A1>=100,Yes,IF(A1<=0,No,""))


Put the 2nd IF statement in the ELSE position of the preceding IF statement.

Perfect - thank you!

 

[dreid1011]

You're welcome.