Excel - IF Function Problem - Expert Solution

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
User 14/03/2018 - 07:47
there's a second sheet I am attempting to copy over
Excelchat Expert 14/03/2018 - 07:47
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc