• 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,OR,AND) combination in a formula

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,
I have two column, A and B
Column A has amounts, B has Age
I am trying to write down below If Or formula but somehow not able to consider all the possibilities correctly
If A is greater than 10 million or age is greater than 7 then C1 value "A"
If A if equal to greater than 5 million and equal to or less than 10 million or age is between 5,6,7 then C1 value "B"
If A is more than 1 million or less than 5 million or age is between 2,3,4 then C1 value "C"
If A is less than 1 million or age is less than 2 then "D"
I tried below combinations but its not working, can anyone please help me in this

'=IF(OR(A2>=10000000,C2>7),"A",IF(OR(A2>=5000000,C2>5),"B",IF(OR(A2>=1000000,C2>5),"C",IF(OR(A2<1000000,C2<2),"D"))))

Please see below combinations for your reference:
A1 value 10 million + 1, B1 value 1, C1 value = A
A1 value 10 million + 1, B1 value 8, C1 value = A
A1 value 100, B1 value 8, C1 value = A

A1 value 5 million, B1 value 1, C1 value = B
A1 value 10 million, B1 value 1, C1 value = B
A1 value 100, B1 value 5, C1 value = B
A1 value 100, B1 value 7, C1 value = B

A1 value 1 million, B1 value 1, C1 value = C
A1 value 4.99 million, B1 value 1, C1 value = C
A1 value 100, B1 value 2, C1 value = C
A1 value 100, B1 value 3, C1 value = C
A1 value 100, B1 value 4, C1 value = C

A1 value 1 millioin, B1 value 1, C1 value = D
A1 value 0.99 millioin, B1 value 1, C1 value = D
 
Last edited:
Hi Sachin, Good Day...

I am confused about your conditions.

If A is greater than 10 million or age is greater than 7 then C1 value "A"

Do you mean it will TRUE if both conditions are TRUE
or any 1 of the condition is TRUE?

What if C = 7 but A less than 5m? (this is a single example)

And you mentioned the combinations with column B also, but there is nothing in your posted formula for this column.

Can you make a table of your conditions and clarify all the possible outcomes?

Regards,
 
Hi @Khalid NGO , thanks a lot for the help. Sorry for making it confusing.

If A1 value is more than 10M or B1 value is more than 7 the C1 value "A".

If A1 value in only 1,00 but B1 value is 8 which is more than 7 then C1 value "A"

I have two column, A with amounts, B with Age, formula is applied in C column.

Really sorry for not uploading sample file.

PS - Sorry for the error in formula, I was considering column A and C and output was column D while checking on my system. But we can consider Column A = amount, Column B = Age, Column C = Output.
 
No problem Sachin,
I need more clarifications, since you are dealing with OR conditions, I think first we need to separate the things like:

Age:
1
2
3
4
5
6
7
8
etc...

What is your expected result on these ages? A, B, C, D?

Amount:
1,000,000
5,000,000
10,000,000
etc...

What is your desired result on these amounts? A, B, C, D?

Regards,
 
Hi @Khalid NGO , thanks a lot for the help. Please see below as per your convenience.

1 = D, 2 = C, 3 = C, 4 = C, 5 = C, 6 = B, 7 = B, 8 = A.

Less than or equal to 1M = D

More than 1M, less than or equal to 5M = C

More than 5M but less than or equal to 10M = B

More than 10M = A
 
Hi @Khalid NGO ,

Please see below table for reference. Not able to upload sample file.
Separator column used just for visual appearance.

AmtseperatorAgeseperatorStatus
1-1-D
1-2-C
999,999-1-D
999,999-2-C
1,000,000-1-D
1,000,000-2-C
1,000,001-2-C
5,000,000-2-C
5,000,000-5-C
5,000,000-6-C
50,000,001-1-B
50,000,001-2-B
50,000,001-3-B
50,000,001-4-B
50,000,001-5-B
50,000,001-6-B
50,000,001-7-B
50,000,001-7-B
50,000,001-8-A
10,000,000-1-B
10,000,000-2-B
10,000,000-3-B
10,000,000-4-B
10,000,000-5-B
10,000,000-6-B
10,000,000-7-B
10,000,000-7-B
10,000,000-8-A
10,000,001-1-A
10,000,001-2-A
10,000,001-3-A
10,000,001-4-A
10,000,001-5-A
10,000,001-6-A
10,000,001-7-A
10,000,001-7-A
10,000,001-8-A
 
Sachin sorry I am still not finding a way you are looking for, there are "OR" conditions which getting TRUE without meting the 2nd condition.

=IF(OR(A2<1000000,B2=1),"D",IF(OR(AND(A2>=1000000,A2<=5000000),B2={2,3,4,5}),"C",IF(OR(AND(A2>5000000,A2<=10000000),B2={5,6}),"B",IF(A2>10000000,"A","--"))))

=IF(AND(A2<=1000000,B2=1),"D",IF(OR(AND(A2>=1000000,A2<=5000000),B2={2,3,4,5}),"C",IF(OR(AND(A2>5000000,A2<=10000000),B2={5,6}),"B",IF(A2>10000000,"A","--"))))
 
Hi @Khalid NGO , please see below code used by me (if you get time), seems to be okay, still auditing.


Code: 
TempLr = AASht.Cells(Rows.Count, 1).End(xlUp).Row
Set TempRng = AASht.Range(AASht.Cells(2, 3), AASht.Cells(TempLr, 3))

For Each Rng In TempRng
    If Rng.Offset(0, 1).Value = "" Then
        If Rng.Value > 7 Then
            Rng.Offset(0, 1).Value = "A"
        End If
    End If
Next
For Each Rng In TempRng
    If Rng.Offset(0, 1).Value = "" Then
        If Rng.Offset(0, -2).Value > 10000000 Then
            Rng.Offset(0, 1).Value = "A"
        End If
    End If
Next
For Each Rng In TempRng
    If Rng.Offset(0, 1).Value = "" Then
        If Rng.Offset(0, -2).Value < 1000001 And Rng.Value < 2 Then
            Rng.Offset(0, 1).Value = "D"
        End If
    End If
Next
For Each Rng In TempRng
    If Rng.Offset(0, 1).Value = "" Then
        If Rng.Offset(0, -2).Value < 5000000 And Rng.Value < 6 Then
            Rng.Offset(0, 1).Value = "C"
        End If
    End If
Next
For Each Rng In TempRng
    If Rng.Offset(0, 1).Value = "" Then
        If Rng.Offset(0, -2).Value > 5000000 Then
            Rng.Offset(0, 1).Value = "B"
        End If
    End If
Next
For Each Rng In TempRng
    If Rng.Offset(0, 1).Value = "" Then
        If Rng.Value > 5 And Rng.Value < 8 Then
            Rng.Offset(0, 1).Value = "B"
        End If
    End If
Next
 
Thanks for the code Sachin, you understand better then me. I am almost zero in VBA.

I have made some changes in my formula, see if the attached file produce something better.
 

Attachments

  • If and or - Sachin.xlsx
    12 KB · Views: 3
@ThrottleWorks I don't understand your code. Its extremely confusing.

What is Rng? which column? A (amount), B (age), C (the output)

The reason it is confusing is sometimes you are referring to Rng.Offset(0,1).Value = "". So I assumed TempRng is B but later you are using Rng.Offset(0,-2).Value for the Amount so I literally have no clue how your file is set up at all and how your macro is working.

Secondly your table is unclear. Please provide a table that is something like below

Code: 
Start Amount | End Amount | Start Age | End Age | Output

    >0       |    <1M     |    0      |    1    |    D
 
Hi @chirayu , thanks for the help. In the original file, Column A is amount, Column C is Age, Column D is output. Sorry for making it confusing.

Rng is Column C, Rng Offset (0,1) is Column D (Output). Rng Offset (0,-2) is Column A which is Amount.
 
ThrottleWorks said:
Hi @Khalid NGO , thanks a lot for the help. Please correct if I am wrong, I am not able to derive results for combination 4999999 and 1. 1000001 and 1.

Thanks for your valuable time. :)
Click to expand...
Yes you are right.

More IFs will be required:
=IF(B2=1,"D",IF(............

But I think we should not use more ifs, formula will become hectic :confused:

I was trying to make a 2way lookup table, but no success so far.

Hope you or someone find a better way.
 
ThrottleWorks said:
Hi @chirayu , thanks for the help. In the original file, Column A is amount, Column C is Age, Column D is output. Sorry for making it confusing.

Rng is Column C, Rng Offset (0,1) is Column D (Output). Rng Offset (0,-2) is Column A which is Amount.
Click to expand...

Thanks @ThrottleWorks. Please can you give me a table in the format I asked you. I'll try to make a macro for you to do the output
 
Hi @Khalid NGO


My initial impression was, I will prepare a cool formula and will avoid loop.

By the way loop is favourite life saver, I am aware it is not efficient all the time but with my limited knowledge I am left with no choice.


However when I tried to write the formula it engulfed me and finally I took help from Loop.

I will try to upload a sample file. Have a nice weekend. :)


Hi @chirayu

Sorry for not uploading sample file, I will try to and will inform you. Thanks for the help, have a nice weekend. :)
 
Hello Sachin, Good Day...

I hope you will find a batter way, I just want to add that I have uploaded the file with 2 result based on your 2 different conditions.
1 is based on Value and other is based on Age.

Can you go through the results and check what would be your desire out put and why?

Regards,
 

Attachments

  • If and or - Sachin-2.xlsx
    11.1 KB · Views: 7
Hi @Khalid NGO , thanks for the help. Please correct me if I am wrong. As per the latest file, the result is populated in two different columns, D and E. I am trying to get the result in one column.

PS - Could you please check value in range H5, I guess there was an extra 0. No issues with that, just as an FYI.

Good night. Shabba Khair. :)
 
Dear ThrottleWorks,

You are being a part of the forum from 12/12/2011 & also having more than 1K posts, after considering that you must to upload a sample file with detail explanation of the output but after analyzing lots of posts i found that you always sucks on that part.We tries to solve the query but without having the actual basics it failed to achieve the goal.

It's not good to say sorry for sample as you can always crate dummy data taking your effort.

@ #19 : @Khalid NGO asked that "Can you go through the results and check what would be your desire out put and why?" but you failed to express your thought yet.

I doesn't intended to be rude, just express what i felt.
 
Hi @Deepak Sir, at present I can not upload sample files due to some reasons. It's not that I do not want to do it, the case is I can not do it.

The problem is uploading files, it's not about creating dummy data.

I guess I missed this. Apology.
Going forward I will take utmost care while posting, will try not to repeat the mistakes. Thanks a lot for the help.

@ #19 : @Khalid NGO asked that "Can you go through the results and check what would be your desire out put and why?"
Click to expand...

Please do not feel offended, good night. :)

PS - Sir I guess, I have replied to Khalid regarding#19.
result is populated in two different columns, D and E.
Click to expand...
 
In a sense, these values seem to ignore the other result. Using nested IF is painful. I got some mixed bag with following formula.
In cell C1 or D1
=CHOOSE(MAX(MATCH(B1,{1,2,6,8},1),MATCH(A1,{1,1000001,5000001,10000001},1)),"D","C","B","A")
 
You must log in or register to reply here.
Back
Top