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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,682
Office Version
  1. 365
Platform
  1. MacOS
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

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

Jeffmonster

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

Well-known Member
Joined
Oct 24, 2012
Messages
5,682
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Jeffmonster

New Member
Joined
Apr 7, 2015
Messages
25
It's a google doc - would it be easier if I shared it with you? Can I share it with you that way?
 

Jeffmonster

New Member
Joined
Apr 7, 2015
Messages
25

ADVERTISEMENT

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

Well-known Member
Joined
Oct 24, 2012
Messages
5,682
Office Version
  1. 365
Platform
  1. MacOS
:"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

Well-known Member
Joined
Oct 24, 2012
Messages
5,682
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Apr 7, 2015
Messages
25
Eureka! Yahtzee!
You are the man!
Thank you so much for your help!

Until next time.... Peace and Chicken Grease!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,155
Messages
5,835,698
Members
430,380
Latest member
Psalmysam

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
Top