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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,387
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
4,387
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
4,387
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
4,387
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,204
Messages
5,594,826
Members
413,942
Latest member
Dhornsby21

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