I have 6 columns and 5 rows of data I need a macro to compare the data in pairs. So if the data in A2 is greater than 30 and the data in B2 is greater than 30 then put the word “Pass” into A7 or “Fail” in A7 if not true. I need this to repeat for pairs C2 and D2, and E2 and F2. Then repeat the same for the remaining rows,see the attached example.
Any help would be greatly appreciated!
Thanks
Jim
In a7:
=CHOOSE(AND(A2>30,B2>30)*1+1,"fail","pass")
Copy down through A10, don't need a macro.
edit- added attachment
Last edited by GeneralDisarray; 06-29-2012 at 10:28 AM.
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
You can do this with a simple formula....put this into A7
if you want to do it with a macro then use
Please Login or Register to view this content.
Please Login or Register to view this content.
this is just a good example of not needing a nested IF statement
General i think its a matter of personal preference. To me my formula makes alot more sense than yours, yet they do the same thing. It just depends on what you are used to.
agree...to a point. the problem is folks take nested IF's too far. Your example is still readable, but add a few more conditions and it becomes a headache. If people get more comfortable with not using IF statements for the easy problems, they don't get trapped using them on the harder cases.
check out this blog, really good read on this: http://www.excelhero.com/blog/2010/01/i-heart-if.html
An argument for nested IFs is that they evaluate faster, which is insignificant in this case, but not in others.
Entia non sunt multiplicanda sine necessitate
You're right, I've seen some pretty gnarly if statements before, and they can be a pain to understand/modify.
You need to make the condition a2+b2 > 60 evaluate to a specific number. If you use this: ((a2+b2)>60)*1+1, you will then have 2 cases: (False)*1+1 which evaluates to 1 or (True)*1+1 which evaluates to 2.Originally Posted by GaidenFocus
You're right, I've seen some pretty gnarly if statements before, and they can be a pain to understand/modify.
hows that?Please Login or Register to view this content.
Then, you tell Choose what the function should return for the 1 and 2 case: "pass" for 1, and "fail" for 2.
Basically, statement like (B1>30) evaluate to TRUE or FALSE and when you use those statements in a multiplication statement, they are read as true=1 and false=0.
Yea i deleted it before you responded simply because if one of the values is 60 or above and the other value is 1 or above it would be a false positive.
Thanks, I have a lot larger file then my example, I was looking for code that would loop through the entire range of each pair of cells in my example, not just "a2" and "b2". If you have any suggestions I would greatly appreciate it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks