Question description:
This user has given permission to use the problem statement for this
blog.
I am looking to return a percentage based of numerous criteria (which are embedded in vlookups). I need the formula to see if a percentage is greater than 0, if so, find the proper segment, rate with that segment, and return the proper percentage.
Solved by O. J. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/02/2018 - 10:18
Hello :)
User
07/02/2018 - 10:19
hello
User
07/02/2018 - 10:19
hope you are doing well
Excelchat Expert
07/02/2018 - 10:20
I am. Thank you. I hope you are as well!
User
07/02/2018 - 10:20
i will be doing better once i can get this working! lol
Excelchat Expert
07/02/2018 - 10:20
lol fair enough.
Excelchat Expert
07/02/2018 - 10:20
What are you trying to do?
User
07/02/2018 - 10:21
so i need to return the rate of commission based off multiple criteria
User
07/02/2018 - 10:21
cant share my whole file as it has company info
User
07/02/2018 - 10:21
the information is on multiple sheets and there are multiple vlookups
User
07/02/2018 - 10:22
i was looking into index, match, if statements
Excelchat Expert
07/02/2018 - 10:22
Okay, index match would be the way to go.
Excelchat Expert
07/02/2018 - 10:22
based on what you told me here.
User
07/02/2018 - 10:22
the growth rate is a formula, so it can pull back negative and positive percentages...if it is negative, it needs to say 0
User
07/02/2018 - 10:23
of if G4>0, please look at the segment that person is in, find the percentage of growth for that segment, and then return that percentage
User
07/02/2018 - 10:24
so since 13% is below 15%, it would come back to the 12.5% rate
Excelchat Expert
07/02/2018 - 10:24
So in your example which column is the segment?
User
07/02/2018 - 10:24
G3 is my reference for the segment
User
07/02/2018 - 10:24
G4 would be the rate
User
07/02/2018 - 10:24
i was trying to make it somewhat simple for myself, lol
Excelchat Expert
07/02/2018 - 10:26
hmmm.
Excelchat Expert
07/02/2018 - 10:26
ah, i see.
User
07/02/2018 - 10:27
sorry, hope i am explaining that properly!
Excelchat Expert
07/02/2018 - 10:27
You are, I'm looking at a solution now.
User
07/02/2018 - 10:27
excellent...thanks for looking into it!
Excelchat Expert
07/02/2018 - 10:28
I have everything except for the last part.
Excelchat Expert
07/02/2018 - 10:28
My pleasure :)
Excelchat Expert
07/02/2018 - 10:29
What is column C, the number you want to return?
User
07/02/2018 - 10:29
correct!
User
07/02/2018 - 10:29
that is the rate I need to come back
User
07/02/2018 - 10:29
for the percentage of growth...for that particular segment
Excelchat Expert
07/02/2018 - 10:30
So is it always a 2.5% increase?
Excelchat Expert
07/02/2018 - 10:30
as far as column B goes?
User
07/02/2018 - 10:31
that is what we have built right now, but it could change
User
07/02/2018 - 10:31
depending on the segment that we are looking at
Excelchat Expert
07/02/2018 - 10:31
Okay, if it changes, the solution I have may need to change.
Excelchat Expert
07/02/2018 - 10:31
However, if that increment stays the same, I have the perfect solution.
User
07/02/2018 - 10:31
we had been talking about utilizing tables...but was trying to avoid that and get it done without that
User
07/02/2018 - 10:31
well thats try that one first!
User
07/02/2018 - 10:31
:D
Excelchat Expert
07/02/2018 - 10:32
Okay, sounds great. One moment while I set it up.
User
07/02/2018 - 10:32
thank you!
Excelchat Expert
07/02/2018 - 10:32
You're very welcome :)
Excelchat Expert
07/02/2018 - 10:45
Please see G6.
Excelchat Expert
07/02/2018 - 10:46
It's not fully complete, but I'm just showing that it'll work.
User
07/02/2018 - 10:47
so you are using the percentage growth rates
Excelchat Expert
07/02/2018 - 10:47
Correct. I'm matching the segment with the growth rate.
Excelchat Expert
07/02/2018 - 10:48
In order to get the growth rates to match, I had to tier them with a massive nested if statement.
Excelchat Expert
07/02/2018 - 10:48
Then, it returns the value that matches both of those numbers in column c.
User
07/02/2018 - 10:48
so i will need to add a column with the growth rate in order to utilize this
Excelchat Expert
07/02/2018 - 10:49
No, I did that for my convenience in writing such a large formula.
User
07/02/2018 - 10:49
oh, lol
Excelchat Expert
07/02/2018 - 10:49
you can eliminate that table by hard-coding that table within the nested if.
Excelchat Expert
07/02/2018 - 10:49
So instead of if(and(G4>=$I$2,G4<I3)
Excelchat Expert
07/02/2018 - 10:50
it would be if(and(G4>=.025,g4<.05)
User
07/02/2018 - 10:50
yep, that makes sense
User
07/02/2018 - 10:50
only trouble would be if we changed the rates then
Excelchat Expert
07/02/2018 - 10:51
I would recommend you use tables though because once you change the rates, you can use a look up to find those numbers also.
User
07/02/2018 - 10:51
yeah, that is where it was starting to go...but i unfortunately am unfamiliar with utilizing those, so was trying to do a formula...sounds like if we are going to be changing things that tables def make more sense
Excelchat Expert
07/02/2018 - 10:52
Yep. absolutely.
User
07/02/2018 - 10:52
thank you very much for your help with this
User
07/02/2018 - 10:52
i really do appreciate it
Excelchat Expert
07/02/2018 - 10:52
You're very welcome. It has been my pleasure :)
User
07/02/2018 - 10:52
now to take it back and apply to my actual spreadsheet!
Excelchat Expert
07/02/2018 - 10:53
Do you understand the formulas I used and why?
User
07/02/2018 - 10:53
actually, as we have a few more minutes...
User
07/02/2018 - 10:53
i see the index is looking at the rate
User
07/02/2018 - 10:54
and you want to match international with an if statement due to the growth rate
User
07/02/2018 - 10:54
so it looks to the segment first
User
07/02/2018 - 10:54
then the growth...
Excelchat Expert
07/02/2018 - 10:54
Yup.
User
07/02/2018 - 10:55
and you have the rates in there which i can hard code
Excelchat Expert
07/02/2018 - 10:55
Oh, when you go to apply this to your spreadsheet, you have to convert it to an array by pressing cntrl+shft+enter.
User
07/02/2018 - 10:55
i did read that from earlier
User
07/02/2018 - 10:55
and it recognizes when you do that?
Excelchat Expert
07/02/2018 - 10:56
Yes, the array just allows you to store multiple concatenated words at the same time.
User
07/02/2018 - 10:56
ahhh, okay
User
07/02/2018 - 10:56
well thank you again for all of your help, i do appreciate you taking the time...and the explanation!
Excelchat Expert
07/02/2018 - 10:57
You're very welcome. Please rate your service after ending the session :)
User
07/02/2018 - 10:57
absolutely!
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.
