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
thanks
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
yes
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
thanks
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
=IF(AND(A1>=1,A1<10),A1*0.25,5)
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
nice
User
08/09/2018 - 08:24
thx
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
https://www.excelchat.co/pricing/personal
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.
