Excel - IF Function Problem - Expert Solution

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
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.

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.