Conditional Formatting - varying rates linked to varying Lead Sources

[Jeffmonster]

Hi Everyone,

I'm seeking advice on what I believe is conditional formatting.

My spread sheet is used to track sales volume for a real estate team. The information I am looking to seek is this:

I have multiple Lead Sources we categorize our leads. 2 of the many leads are subject to a different rate (5%, rather than the usual 10%)
Under that category "Lead Source", I am trying to attach a "rate" at Which the payout amount will automatically reflect the rate according to the source.
For Example: The Two sources; "Radio-KLBJ" and "Listing Inquiry" Would both be compensated at a Rate of 5% of the total commision of the sales price where as an EX/W Outbound is 10%, If that makes sense.

LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203							$200,000.00	$6,000.00	$300.00
EX/W - OUTBOUND - (LM) 402							$200,000.00	$6,000.00	$900.00
RADIO - KLBJ - (LM) 301							$200,000.00	$6,000.00	$300.00

<colgroup><col style="width: 345px"><col width="82"><col width="70"><col width="104"><col width="49"><col width="52"><col width="59"><col width="92"><col width="85"><col width="79"></colgroup><tbody>
</tbody>

Thanks for the looks, consideration and the help.
Jeffmonster

 

[etaf]

is it only the one that is at 10%

=IF( cell with name in = "EX/W Outbound" , cell with value * 0.1 , cell with value * 0.5)
what is the (LM) 402 - does that change

if would help
if you could use a file share site like dropbox or onedrive to put a sample of your workbook, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

 

[Jeffmonster]

Company or Agent Generated	Lead Source	Pre List Package (Y/N)	Listing Agent	List Price $	Comm %	Live Date	Close Date	Sold Price	Commission	ISA Commission
Agent	EX/W - OUTBOUND - (LM) 402	No	Butch						$0.00	$0.00
Company	LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203	No	Butch						$0.00	$0.00
Agent	FSBO - OUTBOUND - (LM) 404	No	Eric						$0.00	$0.00
Company	LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203	No	Eric						$0.00	$0.00
Agent	FSBO - OUTBOUND - (LM) 404	No	David						$0.00	$0.00
Agent	EX/W - OUTBOUND - (LM) 402	No	Eric						$0.00	$0.00
Agent	JL/JS CALL - CIRCLE - (LM) 405	No	Butch						$0.00	$0.00
Company	RADIO - KLBJ - (LM) 301	No	Butch						$0.00	$0.00
Company	RADIO - KLBJ - (LM) 301	No	Eric						$0.00	$0.00
Agent	EX/W - OUTBOUND - (LM) 402	No	Butch						$0.00	$0.00
Company	BUYER INTERNET - KUNVERSION - (BM) 105	No	Beth						$0.00	$0.00
Company	RADIO - KLBJ - (LM) 301	No	Eric						$0.00	$0.00
Company	RADIO - KLBJ - (LM) 301	No	Kim						$0.00	$0.00
Company	RADIO - KLBJ - (LM) 301	No	Butch						$0.00	$0.00
Agent	EX/W - OUTBOUND - (LM) 402	No	Eric						$0.00	$0.00
Agent	BUYER INTERNET - REW - (BM) 101	No	Butch						$0.00	$0.00
Agent	BUYER INTERNET - REW - (BM) 101	No	Butch						$0.00	$0.00
Agent	EX/W - OUTBOUND - (LM) 402	No	Eric						$0.00	$0.00
Company	RADIO - KLBJ - (LM) 301	No	Butch						$0.00	$0.00
Agent	BUYER INTERNET - REW - (BM) 101	No	Huck						$0.00	$0.00
Company	YARD MARKETING - LISTING SIGN - (BM) 202	No	Dan						$0.00	$0.00
Company	BUYER INTERNET - REW - (BM) 101	No	Huck						$0.00	$0.00
Company	BUYER INTERNET - REW - (BM) 101	No	Huck						$0.00	$0.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Eric						$0.00	$0.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Eric						$0.00	$0.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Butch						$0.00	$0.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Eric						$0.00	$0.00
Agent	BUYER INTERNET - KUNVERSION - (BM) 105	No	Beth						$0.00	$0.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Eric						$0.00	$0.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Eric						$0.00	$0.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Eric						$0.00	$0.00
Company	YARD MARKETING - LISTING SIGN - (BM) 202	No	Jeff	$468,000.00	4.50%	13-Feb	10-Apr	$468,000.00	$21,060.00	$4,680.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Eric	$174,900.00	1.00%	27-Feb	10-Apr	$174,900.00	$1,749.00	$262.35
Agent	BUYER INTERNET - CRAIGSLIST - (BM) 109	No	Jeff	$232,500.00	3.00%	1/8/2015	1/8/2015	$232,500.00	$6,975.00	$3,487.50
Agent	EX/W - OUTBOUND - (LM) 402	No	Jeff	$225,000.00	3.00%	24-Jan	13-Mar	$225,000.00	$6,750.00	$1,012.50
Agent	EX/W - OUTBOUND - (LM) 402	No	Eric	$188,000.00	3.00%	25-Jan	10-Apr	$188,000.00	$5,640.00	$846.00
Agent	FSBO - OUTBOUND - (LM) 404	No	Jeff	$150,000.00	2.50%	23-Jan	4-Mar	$150,000.00	$3,750.00	$450.00
Agent	EX/W - OUTBOUND - (LM) 402	No	Eric	$94,000.00	1.00%	9-Mar	20-Mar	$94,000.00	$940.00	$141.00
Agent	FSBO - OUTBOUND - (LM) 404	No	Jeff	$138,750.00	2.50%	23-Feb	1-Apr	$138,750.00	$3,468.75	$423.75
Agent	FSBO - OUTBOUND - (LM) 404	No	Jeff	$203,500.00	3.00%	6-Mar	21-Apr	$203,500.00	$6,105.00	$915.75
Agent	EX/W - OUTBOUND - (LM) 402	No	Eric	$565,000.00	3.00%	13-Feb	13-Mar	$565,000.00	$16,950.00	$2,542.50
Agent	BUYER INTERNET - CRAIGSLIST - (BM) 109	No	Jeff	$182,000.00	3.00%	12-Nov	2-Mar	$182,000.00	$5,460.00	$2,475.00
Agent	REFERRAL - DIRECTLY TO AGENT - (RM) 802	No	Jeff	$156,740.00	3.00%	26-Sep	20-Mar	$156,740.00	$4,702.20	$2,351.10
Agent	JL/JS CALL - CIRCLE - (LM) 405	No	Shawn	$285,000.00	3.00%	10-Jan	2-Mar	$285,000.00	$8,550.00	$1,095.00
Agent	EX/W - OUTBOUND - (LM) 402	No	Jeff	$557,000.00	4.00%	22-Aug	30-Jan	$557,000.00	$22,280.00	$3,342.00
Company	RADIO - KLBJ - (LM) 301	No	Eric	$285,000.00	3.00%	17-Apr	30-Jun	$295,000.00	$8,850.00	$442.50
Agent	BUYER INTERNET - REW - (BM) 101	No	Butch	$66,000.00	3.00%	22-May	26-Jun	$66,000.00	$1,980.00	$297.00
Company	LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203	No	Eric	$169,900.00	3.00%	20-Mar	22-Jun	$169,900.00	$5,097.00	$254.85
Agent	EX/W - OUTBOUND - (LM) 402	No	Jeff	$607,000.00	3.00%	6-Mar	29-Jun	$607,000.00	$18,210.00	$2,731.50

<colgroup><col style="width: 81px"><col width="345"><col width="82"><col width="70"><col width="104"><col width="49"><col width="52"><col width="59"><col width="92"><col width="85"><col width="79"></colgroup><tbody>
</tbody>

Would this work?
So, it doesn't show here, but I did Data Validation for the "Lead Source" so one may only select from the items provided from that list. Your advice may work, I haven't tried it yet, but I'm just not proficient yet with If/Then formulas.

 

[Jeffmonster]

oh and LM 402 isn't anything other than a reference for my boss I think. Code sources he may use, not sure. but those numbers all correlate to something different, yes!

 

[etaf]

i'm not sure i understand what you are trying to do now

 

[Jeffmonster]

It's a google doc - would it be easier if I shared it with you? Can I share it with you that way?

 

[Jeffmonster]

Ok
I've figured some of this out so far...
This is where I am at:
=IF(J10="RADIO - KLBJ - (LM) 301",R10*0.05,R10*0.1)
I've got my formula to correctly compute that if the statement in Cell J10 reads - "Radio - KLBJ" it will recognize that that rate is .05 and multiple corretly. My trouble I am having now, is I need to add :"RADIO - KVET - (LM) 302" & "[FONT=arial, sans, sans-serif]LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203" to be multiplied at .05 as well. Anything else *.1[/FONT]

[FONT=arial, sans, sans-serif]Am I making any better sense here? I appreciate your insight into my issue.


[/FONT]

 

[etaf]

:"RADIO - KVET - (LM) 302" & "LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203

=IF(J10="RADIO - KLBJ - (LM) 301",R10*0.05,R10*0.1)

Change to
=IF(OR(J10="RADIO - KLBJ - (LM) 301", J10="LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203"), R10*0.05,R10*0.1)

 

[etaf]

just seen you want to add another 2 names

=IF(OR(J10="RADIO - KLBJ - (LM) 301", J10="RADIO - KVET - (LM) 302" , J10="LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203"), R10*0.05,R10*0.1)

 

[Jeffmonster]

Eureka! Yahtzee!
You are the man!
Thank you so much for your help!

Until next time.... Peace and Chicken Grease!