• 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.

Please help with nested IF/AND/OR formula

K

kimkay

New Member
Hello!


I need help with what will probably be a very simple forumla for most of you. Here is the problem:


I have a dropdown box with approximately twenty codes meaningful to my company in it, i.e., D01, D02, D08, D09, D10, D99, etc. The final code is NA, and is to be selected when there is no need for this particular type of code.


I have written this formula for cell J4:


=IF(OR(C4="D01",C4="D08")*AND(I4<50),I4,50)


It works perfectly for Codes D01 and D08, for which the value in Column I can be anything greater than 1.0 (no high limit), and which can be matched by the company at an even dollar amount up to 50.00 -- that is the limit of the match. So, if code D01=1.00, cell J4 will return 1.00; if D4=40.00, J4 will return 40.00; if D4=60.00, J4 will return 50.00.


This is what I need to add to this formula: if C4=D02, D09, D10, D99, NA or any of the codes in the dropdown list OTHER than D01 or D08, J4 should return no value, or blank -- in other words, the only codes with a company match are D01 and D08.


I've tried everything I know how to do, and keep ending up with J4=50.00 for all codes. Any help would be most appreciated!


Thank you,

Kim in Tennessee
 
Hi Kim


The way I would approach this would be to use a helper column. There is probably a way to do this all in the one cell, but i find helper columns a good way of seeing the in-between steps. Try this:


1. Create a lookup table of all of your twenty (approx) codes out to the side or on a spare sheet. Against each of the codes have either "Yes" for D01 and D08, and "No" for every other code.

2. Back on your data table, in any spare column (Assume Column Z for instance) create a vlookup formula to reference "Yes" or "No" from this lookup table.

3. Then you can modify your column J formula with an additional IF statement at the start of the formula, which would then look something close to this:


=if(Z4="No","",IF(OR(C4="D01",C4="D08")*AND(I4<50),I4,50))


I hope that helps

cheers

rob
 
Thank you, Rob! Unfortunately, this is another employee's spreadsheet that isn't terribly efficient and I was trying to help the end-users (data-entry personnel) by making it a bit more user-friendly; a formula change might not feel like a threat to her, but I think a new column/worksheet would. Plus, this is a single part of a very complex spreadsheets with a ton of vlookups (that were all done for her by our IT department); I was hoping for a relatively simple solution that wouldn't add to the complexity of the thing but would alleviate the need for a human to think through the variables possible in cell J4. I'm open to proposing the helper to her if I can't come up with a single formula, though. I appreciate your input, and I'll definitely hang onto it for the future!


Kim
 
No worries Kim, yes it pays to keep it to minimal / simple change where other people are the primary user.


Have you tried adjusting your initial formula to the following:


=IF(OR(C4="D01",C4="D08"),IF(OR(C4="D01",C4="D08")*AND(I4<50),I4,50),"")


that may eliminate the helper column
 
Hi Kimkay,


I believe this formula should work :


Code: 
=IF(OR(C4="D01",C4="D08"),IF(I4<50,I4,50),"")
 
Okay, now I'm more confused: both of your formulas return only the word "TRUE" or the word "FALSE"! I'm not getting a numeric return at all...hmmm...I'll work on these and see if I can get a value or blank to return. Thanks for the input, I'm sure we're close!


Kim
 
Woohoo, I used Robopottamus and GCExcel's suggestions as a jumping-off point and got it to work! Here's the formula in case others might benefit from it:


=IF(AND(C4="D01",I4<50),I4,IF(AND(C4="D08",I4<50),I4,IF(AND(C4="D01",I4>=50),50,IF(AND(C4="D08",I4>=50),50,""))))


I was making it too difficult; when I realized all I had to do was make it work for the two codes that needed to have a result in J4 and let all the other possible results return a blank cell, it was easy!


Thanks for setting me on the right path; I love a good collaboration!

Kim
 
Hi kimkay,


I don't understand why you get TRUE and FALSE with the formula I suggested.

Did you try evaluating the formula step by step ?

You should have the same result as the formule you found.
 
You must log in or register to reply here.
Back
Top