+ Reply to Thread
Results 1 to 19 of 19

IF/AND Formula in a cell, cross checking other cells then displaying them in another cell

  1. #1
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    IF/AND Formula in a cell, cross checking other cells then displaying them in another cell

    Hi guys!

    Cant believe how much help i have recieved on this forum upon starting my Excel Journey. Im learning so much from everyone and i ofthen thought WHY people are willing to help!? I had a reply from someone who has helped me recently explaining that 'We pay it forward...' & that when im in a position to help others thats the return!

    This is so true!.. I hope i can repay people with my knowledge someday too.



    Here is an overview of what im currently trying to achieve,


    I have Cells C10-C19 which contain Names of People.

    Directly to the right of these Names in Cells D10-D19 i have the Scores for these Names.

    Directly to the right of the Scores in Cells E10-E19 i have Blank Cells where i need the Result of two seperate Formula's (explained below) to Display the Name with a specific score.

    Additionally i have a Cell Range A38-A46 that also need to be Checked. If any of these cells contain 'X'... then Display 'X' next to the Name rather than the name itself.
    If the Cells Range A38-A46 are blank or contain 'Y' (basically anything other than 'X') then Display the Name next to the Name.



    I need your help/advice with the following 2 seperate Formulas;

    I have a Cell (N9) which i need to perform the following.

    1) IF Cell A35 is '0' AND Cell A36 is '2'

    AND

    2) IF Cells within range A38-A46 DONT contain 'X'

    THEN

    3) Display the Name in range C10-C19 which is next to the LOWEST score between (and including) 2.00 - 2.99 from range D10-D19 in the corresponding Cell to the right (range E10-E19)

    AND

    4) Display 'Y' in the original Cell N9

    BUT

    5) IF Cell A35 is NOT '0' OR Cell A36 is NOT '2'

    OR

    6) IF Cells within range A38-A46 DO contain 'X' then display 'X' in the original Cell (N9) only

    ------------------------------------------------------------------------------------------

    The other formula i need is a variation of the 3) rule above;

    ------------------------------------------------------------------------------------------

    I have a Cell (N10) which i need to perform the following.

    1) IF Cell A35 is '0' AND Cell A36 is '3'

    AND

    2) IF Cells within range A38-A46 DONT contain 'X'

    THEN

    3) Display the Name in range C10-C19 which is next to the HIGHEST score between (and including) 2.10 - 2.50 from range D10-D19 in the corresponding Cell to the right (range E10-E19)

    AND

    4) Display 'Y' in the original Cell N10

    BUT

    5) IF Cell A35 is NOT '0' OR Cell A36 is NOT '3'

    OR

    6) IF Cells within range A38-A46 DO contain 'X' then display 'X' in the original Cell (N9) only



    Im so appreciative of the help/advice you guys have given on my Excel journey thus far! I hope to be able to do the same in the future.

    I honestly cant thank you enough!

    Thanks for reading

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    That's too much for my wee brain to take in without seeing a sheet. So...

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Sorry Glenn! When i was half way through posting i thought that people may need to see the Spreadsheet.
    Its only my 3rd post and still figuring out how to use Forums properly.

    I have attached x2 seperate Spreadsheets 1) For the first formula im trying to create & 2) For the second.

    Ive also added some notes of the rules criteria again at the top of each spreadsheet and i have highlighted certain relevant cells.

    Thank you SO much for taking the time to help, i really do appreciate any help and advice i recieve!

    Let me know if you need any more information or need me to explain what im trying to achieve more clearly.

    Cheers!

  4. #4
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Sorry to Bump this post guys.

    Ive been struggling with this all day and now taking it home to work on. ANY help or advice would be much appreciated!

    Thanks for reading and hopfully some kind suggestions

  5. #5
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Anything at all guys, or should i be breaking this down alot and see if thats easier for people to respond?

    Ta Muchly

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Quote Originally Posted by Ourkid123uk View Post
    Anything at all guys, or should i be breaking this down alot and see if thats easier for people to respond?

    Ta Muchly
    Yes. Try it. It can not hurt.
    Dave

  7. #7
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Sorry again to bump this guys.

    Im so stuck & could do with your help

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    I've only looked at No 1, so far. Check this over...

    in E10, copied down, an array formula.:

    =IF(AND($A$35=0,$A$36=2,COUNTIF($A$38:$A$46,"X")=0),IF(D10=MIN(IF($D$10:$D$19-2>=0,$D$10:$D$19)),C10,""),"")

    In N9:

    =IF(COUNTIF($A$38:$A$46,"X")>0,"X",IF(COUNTIF($E$10:$E$19,">""")=1,"Y",""))

    I think that this is what you need. Check it over carefully!!


    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    Incidentally, i see that you are using Excel 2016. is there any reason why you need backward compatability with Excel 97-2003. Using .xlsx is far superior to .xls
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Glenn, thankyou!

    After testing it seems perfect.

    I should be able to figure out the second Formula from this, much appreciated!

    Just a question though...

    Because i have 2 Formulas that need to display the name E10 - E19 they would conflict wouldnt they? Any way or putting the array Formula you have created within the Cell
    N9 and then have it just display the result in the corresponding Cell?

    Appreciate it muchly.

    Ow yes unfortunatley i need the backwards compatabilty, otherwise i would save as .xlsx

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    You would still need a formula in every cell in the range E10-E19 to do that. You might have to have the result of your second formula in another group of cells. would that work??

  11. #11
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Yes! Excellent thank you Glenn, you have been such a help.

    I have managed to Sort the result of my second formula in another group of cells, im then using either VLOOKUP to reference the result back to my desired cell or will have to
    look into INDEX MATCH (i believe is better once you can get your head around it).

    So the final part im struggling on is probably the simplest (i know! But i am still learning)

    Ill demonstrate showing the whole Formula and then Isolate the part of the formula which needs adjusting correctly.

    {=IF(AND($A$35=0,$A$36=2,COUNTIF($A$38:$A$46,"X")=0),IF(D10=MIN(IF($D$10:$D$19-2>=0,$D$10:$D$19)),C10,""),"")}

    the above formula is going to be copied into lots of different cells but i require 2 variations.

    this is the part of the formula that needs adjusting for my 2 variations;

    IF(D10=MIN(IF($D$10:$D$19-2>=0,$D$10:$D$19)),C10,"")

    At the moment this returns the 'Lowest value within the 2's' (2.00 - 2.99)

    Variant 1)

    I need this adjusted to return the 'Lowest value between the range of 1.50 - 1.90'

    Variant 2)


    I need this adjusted to return the 'Highest value between the range of 2.05 - 2.35'


    All the Cell references are correct and stay the same its just the Selection of the Highest or Lowest Value within a range that i need it to return.

    Cheers guys!

    ps,

    In the original Formula;

    IF(D10=MIN(IF($D$10:$D$19-2>=0,$D$10:$D$19)),C10,"")

    I dont fully understand how its selecting the Lowest Value within the 2's (or between the range 2.00 - 2.99). After the Cell range why does it have "-2>=0"?
    How does that work whats the process?

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    can you re-post the sheet with it in place (and tell me where it is, if it isn't obvious...)?

  13. #13
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Here you go Glenn. Let me know if you require any more information or explaining. Basically i think i just need to use a MIN / MAX Function within a range of numbers but i just cant figure it out.

    Thanks alot for your help, its greatly appreciated.
    Attached Files Attached Files

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    There's a fault in your logic... I think. Your desired range is 1.5 to 1.9. if there are ANY values within that range, then A41 becomes 1 and therefore the formula should return... nothing??? the same applies to your column E, too. If any value is between 2 and 2.3, A41 becomes 1 and the formula returns nothing. Incidentally, the formula in E could be fixed up a bit; but I'll await advice on your criteria - which do seem a bit contradictory.
    Last edited by Glenn Kennedy; 03-24-2017 at 01:49 PM.

  15. #15
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Glenn! Of course... sorry i was just trying to write an example as the data i use changes alot.

    It really doesnt matter about the Error that the Cell A41 would return from any of the Variants and the one you got working. I can change that part so it all works error free. eg,
    I can change the ranges after or i could allow there to be 1 in Cell A41. The real part im struggling with is the "Returning the LOWEST value within the range of two numbers" and "Returning the HIGHEST value within the range of to numbers" as the middle part of the Formula.

    I have attached an updated sheet where Cell A41 would not return an Error.

    Sorry for the confusion with my numbers, its because they are always changing. As i say i can adjust them all after its just getting my head round the LOWEST value and HIGHEST value within a certain range of numbers.

    Many thanks Glenn. Ive tried to give you more positive feedback but it says i need to do it for others. Unfortunatly your the only person who has taken your time out to help. Truley appreciated.
    Attached Files Attached Files

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    I'm away out now. I'll look back in the morning.

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    I've simplified the formula in column E. It's easier to follow!! I've adapted it for G & H, too.

    Is this doing what you want? I get confoosed by your verification checks!! Incidentally, why do you save your files in .xls format? You have Excel 2016 - which is far more powerful than 2003 (PC versus abacus...)
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    02-09-2016
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    224

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    Glenn!

    Thank you, that works perfectly and the undated verion now makes more sense to me as it in the same format as the two variants. I need it to be compatible with an older version of Excel as it will be linked to an older verion at somepoint.

    The good thing about Formulas is there are multiple ways to achieve one outcome. I have learned alot so far and again, thank you for taking the time to help Glenn.

    SOLVED!

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: IF/AND Formula in a cell, cross checking other cells then displaying them in another c

    You're welcome!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Leaving a cell (with formula) displaying as empty if theres data in another cell
    By bluenose_1878 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-27-2015, 12:27 PM
  2. VBA code to return a count in a cell after cross checking three different criteria
    By jmartineau in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2013, 03:40 PM
  3. [SOLVED] Formula for checking a cell and determining data set based on cell value
    By fauxreality in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-18-2013, 11:40 AM
  4. Replies: 5
    Last Post: 02-25-2010, 12:43 PM
  5. checking cell contents and displaying a message
    By brownstone in forum Excel General
    Replies: 4
    Last Post: 05-24-2007, 05:54 PM
  6. A formula for displaying the cell reference of the selected cell
    By Emlou85 via OfficeKB.com in forum Excel General
    Replies: 3
    Last Post: 03-19-2006, 11:55 AM
  7. Replies: 1
    Last Post: 03-16-2006, 07:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1