Question description:
This user has given permission to use the problem statement for this
blog.
if Column AA = Yes, and Column X <> to Renewal then Column R Should be assigned to New Business Rep
Solved by A. J. in 46 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/03/2018 - 07:44
Hello. Can you share the spreadsheet in question with me?
Excelchat Expert
14/03/2018 - 07:44
or do you want me to write the formula based on the description?
User
14/03/2018 - 07:46
Can you see the data I pasted to the left in the google sheet
Excelchat Expert
14/03/2018 - 07:47
Yes
User
14/03/2018 - 07:47
there's a second sheet I am attempting to copy over
Excelchat Expert
14/03/2018 - 07:47
Okay.
User
14/03/2018 - 07:49
Okay all data has been posted
Excelchat Expert
14/03/2018 - 07:50
Okay. With this data, can you tell me what you would like the result to be?
User
14/03/2018 - 07:50
so the scenario is we have an opportunity that is being split between our client experience representative and our new business representative
User
14/03/2018 - 07:51
I am trying to get the total value of the invoice split out so it populates a report with the correct splits
Excelchat Expert
14/03/2018 - 07:53
Okay. The formula you posted originally, where would post its results and when you say assigned to a new business rep, does that mean simply mark it like "New Bus. Rep" or do you want it to assign a business rep's name?
Excelchat Expert
14/03/2018 - 07:53
**The formula you posted originally, where would you want it to post its results to**
User
14/03/2018 - 07:55
so that's where sheet two comes into play because there we have the percent that each person is supposed to receive so I don't know if it's easier to do it based on the total invoice value or taking that percentage for each line item and applying it to the last 4 columns of the worksheet
Excelchat Expert
14/03/2018 - 07:57
are these the only two regions? East
CX East
User
14/03/2018 - 07:58
for this instance yes but we have other teams within the company
Excelchat Expert
14/03/2018 - 07:59
Okay, so this is easy enough: if Column AA = Yes, and Column X <> to Renewal then Column R Should be assigned to New Business Rep, but it seems there is more to this problem than that, correct?
User
14/03/2018 - 08:00
yeah in theory its simple
Excelchat Expert
14/03/2018 - 08:00
Column R needs to be assigned to two business reps who split column Rs value?
Excelchat Expert
14/03/2018 - 08:04
I think the solution could be fairly simple once I understand how column R is assigned to the different reps and the percentages
User
14/03/2018 - 08:06
So we have a renewal pool amount that has to be met, and anything over that goes to the new business rep. The data within this sheet that would inidicate what is renewal is column M that has the product license name
User
14/03/2018 - 08:07
Anything that is tagged renewal at the end should be credited to user 2 while the expansion license and Professional learning bundle should be assigned to user 1
User
14/03/2018 - 08:07
Our CRM program though assigns the percentage to each linie item
Excelchat Expert
14/03/2018 - 08:10
Does split 1 and split 2 refer to user 1 and user 2 respectively?
User
14/03/2018 - 08:10
yes
Excelchat Expert
14/03/2018 - 08:11
Okay. I'm going to write formulas in AC and AE to calculate the $ amount by using the percentages on Sheet2 depending on if its a renewal or not. Does that make sense, or no?
User
14/03/2018 - 08:12
That sounds right
Excelchat Expert
14/03/2018 - 08:14
How does that look?
User
14/03/2018 - 08:19
what would need to happen to get the whole value from R to populate in the formulas you created
Excelchat Expert
14/03/2018 - 08:19
You mean have AC + AE = R?
User
14/03/2018 - 08:20
sorry I may have confused my instructions
Excelchat Expert
14/03/2018 - 08:21
SO don't multiply R by the user 1 and user 2 percentages on sheet2?
Excelchat Expert
14/03/2018 - 08:21
Sorry, didn't mean all caps so
User
14/03/2018 - 08:21
so the total deal value in this case was 265,900.. user 1 should be credited with 202,900 or rows 2 and 5 R value while user 2 should be getting R value for 1,3 and 4
User
14/03/2018 - 08:23
the percentages from sheet 2 represent the portion of column T which is the expected revenue for the entire deal
Excelchat Expert
14/03/2018 - 08:24
Like that?
User
14/03/2018 - 08:25
yes
Excelchat Expert
14/03/2018 - 08:26
Cool. Is that the end results you were looking for?
User
14/03/2018 - 08:26
Yes. Thank you for the assist
Excelchat Expert
14/03/2018 - 08:26
No problem!
Excelchat Expert
14/03/2018 - 08:27
Glad we could figure it out
Excelchat Expert
14/03/2018 - 08:27
Make sure to click Done or Complete to conclude the session
Excelchat Expert
14/03/2018 - 08:27
Come back if you need anything else!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
