• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to use "IF" and "OR" condition in one cell

A

alibilal

Member
Hi, I am looking to perform two functions in one cell.
A1=4, B1=16, B1-A1=12

1. Cell C1 should not be more than 8 if value is "C1<=8"
=IF(C1<=8,D1,8)
2. But at the same time what if the value in C1>8, for example 7.5 or 5. I want that value to be displayed in D1. So value less than or equal to 8 should be the outcome in D1.

I await help asap :)
 
Welcome to the forum!

In D1, put:
=MIN(C1,8)
If C1 is less than 8, will take that value. Otherwise, 8 will be the smallest number, and will use that.

Must admit, a little confused as to which cells you are referencing, as I think they got jumbled up in your post. :(
 
I don't know what you mean, partly due to confusion in initial post. You first mentioned A1 and B1, but I don't know how those are used. Then we talk about C1, and check if C1 is less than or equal to 8. Then you said "what if C1 is greater than 8", but gave example of 7.5 or 5, both of which are still less than 8.

If you can clarify what your actual inputs are, and what type of output you need, I may be able to further elaborate.

In short:
1. What cell(s) has the input?
2. What cell has the formula?
3. What are we checking?
 
First of all thanks for your quick reply and secondly my apologies for not being clear ... i will try to elaborate

The Values in Cells are for eg.
A1=any number for eg. 4
B1= for eg. 16
So the Sum of B1-A1=12
and i want the out put in Cell C1 <= 8 or if the sum is less than 8 then that should show in C1

Now in Cell C1 the out put i need is not suppose to be more than "8" or for example if the sum of B1-A1= was less than 8 then that value should show in Cell C1 as actual sum of B1-A1 .. for eg. 8-4=4 ... the out put in C1 should be 4
 
In C1, put this formula then:
=MIN(B1-A1,8)

No need for IF/OR function. The MIN says that if B1-A1<=8, take that number. Otherwise, use 8. I believe this is what you are wanting. :)

PS. If you want to use an IF, it means more typing and repeat of some stuff, but would be:
=IF(B1-A1<8,B1-A1,8)
 
i guess i am still not clear ..
in C1 the output i need is the sum of B1-A1
so if B1=16 and A1=4 the sum in C1 would be B1-A1=12
so C1=12 ... but i dont want any thing more than or equal to 8 in C1
now what if B1=10 and A1=5 .. the sum in C1 should be B1-A1=5 .. how can i have both the conditions for one cell i.e C1 ...
 
Did you try putting my formula in C1?? Because what you described is exactly what happens.
 
Hi @alibilal
Its better you upload a sample file with some data and some examples...

Sir Luke's formula is fulfilling your requirement but we dont know what you need other than the solutions above.
 
I have one more request if you can help me here .. i am attaching the file for quick reference and the notes are in the file
 

Attachments

  • Test Book 123.xlsx
    11.3 KB · Views: 2
Hi, i need some help in designing a worksheet .. i have tried to be as clear as i can be in the excel sheet that i am attaching in. Please note that there are multiple conditions for cell "D" as mentioned in the excel file. I hope you be able to help me.
 

Attachments

  • Book1.xlsx
    9.8 KB · Views: 3
You must log in or register to reply here.
Back
Top