Conditional Formatting - varying rates linked to varying Lead Sources

Jeffmonster

New Member
Joined
Apr 7, 2015
Messages
25
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
Company or Agent GeneratedLead SourcePre List Package (Y/N)Listing AgentList Price $Comm %Live DateClose DateSold PriceCommissionISA 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.004.50%13-Feb10-Apr$468,000.00$21,060.00$4,680.00
Agent



REFERRAL - DIRECTLY TO AGENT - (RM) 802



No



Eric



$174,900.001.00%27-Feb10-Apr$174,900.00$1,749.00$262.35
Agent



BUYER INTERNET - CRAIGSLIST - (BM) 109



No



Jeff



$232,500.003.00%1/8/20151/8/2015$232,500.00$6,975.00$3,487.50
Agent



EX/W - OUTBOUND - (LM) 402



No



Jeff



$225,000.003.00%24-Jan13-Mar$225,000.00$6,750.00$1,012.50
Agent



EX/W - OUTBOUND - (LM) 402



No



Eric



$188,000.003.00%25-Jan10-Apr$188,000.00$5,640.00$846.00
Agent



FSBO - OUTBOUND - (LM) 404



No



Jeff



$150,000.002.50%23-Jan4-Mar$150,000.00$3,750.00$450.00
Agent



EX/W - OUTBOUND - (LM) 402



No



Eric



$94,000.001.00%9-Mar20-Mar$94,000.00$940.00$141.00
Agent



FSBO - OUTBOUND - (LM) 404



No



Jeff



$138,750.002.50%23-Feb1-Apr$138,750.00$3,468.75$423.75
Agent



FSBO - OUTBOUND - (LM) 404



No



Jeff



$203,500.003.00%6-Mar21-Apr$203,500.00$6,105.00$915.75
Agent



EX/W - OUTBOUND - (LM) 402



No



Eric



$565,000.003.00%13-Feb13-Mar$565,000.00$16,950.00$2,542.50
Agent



BUYER INTERNET - CRAIGSLIST - (BM) 109



No



Jeff



$182,000.003.00%12-Nov2-Mar$182,000.00$5,460.00$2,475.00
Agent



REFERRAL - DIRECTLY TO AGENT - (RM) 802



No



Jeff



$156,740.003.00%26-Sep20-Mar$156,740.00$4,702.20$2,351.10
Agent



JL/JS CALL - CIRCLE - (LM) 405



No



Shawn



$285,000.003.00%10-Jan2-Mar$285,000.00$8,550.00$1,095.00
Agent



EX/W - OUTBOUND - (LM) 402



No



Jeff



$557,000.004.00%22-Aug30-Jan$557,000.00$22,280.00$3,342.00
Company



RADIO - KLBJ - (LM) 301



No



Eric



$285,000.003.00%17-Apr30-Jun$295,000.00$8,850.00$442.50
Agent



BUYER INTERNET - REW - (BM) 101



No



Butch



$66,000.003.00%22-May26-Jun$66,000.00$1,980.00$297.00
Company



LISTING INQUIRY - CALL/INTERNET/ETC - (BM) 203



No



Eric



$169,900.003.00%20-Mar22-Jun$169,900.00$5,097.00$254.85
Agent



EX/W - OUTBOUND - (LM) 402



No



Jeff



$607,000.003.00%6-Mar29-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.
 
Upvote 0
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!
 
Upvote 0
i'm not sure i understand what you are trying to do now
 
Upvote 0
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]
 
Upvote 0
:"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)

 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,218,625
Messages
6,143,570
Members
450,493
Latest member
Woejeber

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top