Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi I need to calculate several IF AND OR statements. 1) if value is =1 return 1 2) if value is = 2 return 2 3) if value is greater then 10 but less then 50 multiple by .10 (10%). if value is greater then 5 return 5 4) if value is greater then 50 multiple by .25 (25%). if value is greater then 10 return 10
Solved by K. A. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 08/09/2018 - 08:12
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User 08/09/2018 - 08:13
User 08/09/2018 - 08:13
what am I supposed to do now?
Excelchat Expert 08/09/2018 - 08:14
Okay, I've read your problem statement and it seems you need help with making a nested IF statement. Do you have a file you're currently working on?
User 08/09/2018 - 08:14
no I need to create this calculation
Excelchat Expert 08/09/2018 - 08:15
I see, noted on that. We can just create a sample data then in the document preview to the right.
Excelchat Expert 08/09/2018 - 08:15
Can you see the values to the right?
User 08/09/2018 - 08:15
User 08/09/2018 - 08:17
if value is 1 return 1 but if value is >1 <10 return 2
Excelchat Expert 08/09/2018 - 08:17
There are some conflicting conditions in your problem statement. It would be best if we identify first the expected output for each value based on what you've identified.
Excelchat Expert 08/09/2018 - 08:17
Okay I see. Can you as well explain further the other conditions then?
Excelchat Expert 08/09/2018 - 08:18
What if the value is 10?
User 08/09/2018 - 08:18
if value is > 10 but less then 50 multiple by .25. answer returned can not be more then 5. if it is more then 5 return 5
Excelchat Expert 08/09/2018 - 08:19
Noted on that. What if the value is 10?
User 08/09/2018 - 08:19
the first condition is >1 >= 10
Excelchat Expert 08/09/2018 - 08:20
Noted on that. I was confused back then since the condition provided was only <10. But let me take that then into consideration.
User 08/09/2018 - 08:20
then last condition is 51+then multiple by .10 but value retuned can not be greater then 25
Excelchat Expert 08/09/2018 - 08:21
Noted on that.
User 08/09/2018 - 08:21
so there are 4 conditions
User 08/09/2018 - 08:22
User 08/09/2018 - 08:22
this is what I started with but cant seem to get past it =IF(AND(A1>=5,A1<50),A1*0.25,5)
User 08/09/2018 - 08:23
Excelchat Expert 08/09/2018 - 08:23
I can provide you with a simpler formula that doesn't actually need the AND part, just a nested IF equation.
User 08/09/2018 - 08:23
User 08/09/2018 - 08:24
Excelchat Expert 08/09/2018 - 08:24
Already done! Please see the document preview for your review.
Excelchat Expert 08/09/2018 - 08:24
In cell C3, the formula would be: =IF(B3>50,MIN(25,B3*0.1),IF(B3>10,MIN(5,B3*0.25),IF(B3>1,2,1)))
Excelchat Expert 08/09/2018 - 08:25
This first checks from highest value to lowest according to the conditions provided.
Excelchat Expert 08/09/2018 - 08:26
So basically, it first checks if the value is more than 50. If so, it checks if this value times 0.10 with 25 and returns the smaller value.
Excelchat Expert 08/09/2018 - 08:26
If not, it then checks if the value is more than 10. If so, it again checks if this value times 0.25 is smaller than 5, and returns the smaller value.
Excelchat Expert 08/09/2018 - 08:27
On the third IF, it just checks if the value is above 1. If so, it returns 2. Otherwise, it just returns 1.
User 08/09/2018 - 08:28
I see, ya that is so much easier. thx.
User 08/09/2018 - 08:28
is there a membership to this site?
Excelchat Expert 08/09/2018 - 08:28
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
Excelchat Expert 08/09/2018 - 08:28
yes there is!
Excelchat Expert 08/09/2018 - 08:28
You can check out our subscription pricing in the link below:https://www.excelchat.co/pricing/personal
Excelchat Expert 08/09/2018 - 08:29
Excelchat Expert 08/09/2018 - 08:29
I would recommend the monthly subscription so that you'd be able to ask unlimited questions.
User 08/09/2018 - 08:29
Nice. can I get this emailed to me for now?
User 08/09/2018 - 08:29
Ya monthly will be necessary. :}
Excelchat Expert 08/09/2018 - 08:30
Please download the attachment. :)
[Uploaded an Excel file]
Excelchat Expert 08/09/2018 - 08:30
This contains the document preview to the right. :)
User 08/09/2018 - 08:30
great. thanks.
Excelchat Expert 08/09/2018 - 08:30
If you don't have any more clarifications at this point, you may end the session by clicking the END SESSION on the upper right of your screen.
Excelchat Expert 08/09/2018 - 08:30
Also, I'd appreciate if you could drop a few lines for your great feedback on a survey after this session. Many thanks and have a great day!
User 08/09/2018 - 08:31
will do. have a good day as well

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