How can I join multiple AND OR and IF statements in Excel?

[tbrookes3]

Hello

I have a spreadsheet with ~ 10,000 rows and 40 columns. I'm trying to find a way to easily identify which rows need to have some data updated.

Here's what I have:

IF (OR(BW2 = "Last 12 months" & AV2, AR2 OR AW2 are blank; display the words "Update Required" in CA2

IF BW2= "LAST 12 MONTHS" AND AX2="INCIDENT" & Y2, AA2, AC2, AE2, AZ2, OR BA2 is blank ; display the words "Update Required" in CA2

IF BX2 = "OPEN" AND AV2, AR2, AW2 is blank; display the words "Update Required" in CA2

but I can't work out how to join them together or the right formula to use in between.

Any help is greatly appreciated.

Thanks

[TheN]

Ok.

First of all, to check if something is blank, you use isblank(). So you are testing if AV2, AR2, AW2, Y2, AA2, AC2, AE2, AZ2, BA2, AV2, AR2, AW2 is blank. so you put them all into your formula with this syntax:

isblank(AV2)

your other logical tests are or() and and()

they go in this format:

or(1st test, 2nd test)
and(1st test, 2nd test)

So, you have to combine them in groups of 2.

I'm not 100% clear on your logical choices of OR vs AND, so I'm not going to write the formula for you, since it will probably not work as you desire. Just know that AND() requires both of them to be true to return true, while OR() requires just one option to be true.

Finally, IF() is going to be your outside option (I strongly suggest working from the middle out as you construct this formula). Your IF() statement should be in this format:

IF(test,"Update Required","")

That will give you Update Required if true, and nothing if it is false.

Furthermore, it sounds like you are trying to identify cells that need to be updated, so instead of concocting some monster formula to identify all these criteria, why not use conditional formatting to highlight the actual cells you need to update. Then, if it is that crazy, you can actually filter out everything row by row as you fix it. Press Ctrl+Shift+L to apply a filter, then click one of the filter arrows and filter by color. This can be done column by column so you can update criteria by criteria, instead of having to manually look at each individual cell in your row. Just a suggestion.

To apply conditional formatting, you can find it in the home tab. Click highlight cell rules, then you would want to go to more options and apply formula. From there, you can enter individual rules for each column. So for example, you click AV2 then go to conditional formatting and enter =isblank(AV2) as your rule, then change your range to the whole column (copy the cell, highlight column paste special formatting). Repeat this process for each individual column and it will make your life easier in the future.

[sanram]

Your requirement is not clear to me. Please upload a sample copy with the output you want or write those 3 formulas separately with excel formulas. Then I can join them.

Now as I have understood, here is the combined formula may be :

Please Login or Register  to view this content.

[tbrookes3]

Hello

Please see below the formulas I need to join together. I think these are right.

=IF((BW2 = "Last 12 months"),and (if(isblank(AV2), isblank(AR2),isblank(AW2),"Update Required","OK"
=IF(BW2="Last 12 months", AND IF (AX2="Incident"), AND IF(OR(ISBLANK(Y2),ISBLANK(AA2),ISBLANK(AC2),ISBLANK(AE2)),"Update Required","OK"))
=IF(AND(BX2="OPEN"),IF(OR(ISBLANK(AV1),ISBLANK(AR1),ISBLANK(AW1)),"Update Required","OK"))
If any of the above groupings are true; then I want the formula to display the words "Update Required".

I would be happy to explain further. I have also attached a sample workbook.

Thankyou for your time

[alansidman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[tbrookes3]

Post title amended

[TheN]

hi, you almost have it.

You are so close to achieving the answer on your own, I'd rather you solve it than giving you the answer. One more hint:

You can only join 2 things in AND() and OR(). If you want to combine more than 2 things with these functions, you need to nest them.

For example, you put

OR(ISBLANK(Y2),ISBLANK(AA2),ISBLANK(AC2),ISBLANK(AE2))

To make this function work in the way you want it to, you need to use this type of syntax:

OR(OR(ISBLANK(Y2),ISBLANK(AA2)),OR(ISBLANK(AC2),ISBLANK(AE2)))

Basically, once you combine two of them with an OR() function (or an AND() function) you can treat it as one statement in your next logical test.

Hope this helps.

[sanram]

Try this:

Please Login or Register  to view this content.