+ Reply to Thread
Results 1 to 31 of 31

Comparing Text and numbers to output a text

  1. #1
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Comparing Text and numbers to output a text

    I have a matrix table with a mixture of text High, Medium, Low, Event (see attached pic)

    I want to write to a cell a word based on comparing two cells.

    e.g.

    If Cell A1 = Soon and Cell B1=4, I want it to show in Cell C3 = Medium
    If Cell A1 = Automated and Cell B1=5, I want it to show in Cell C3 = High

    I need to do this for 30 different checks based on the above. Basically comparing an urgency - cell A1, with a Consequence number (B1)

    Any ideas how I best do this?
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by alanatabz; 01-19-2021 at 10:14 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    can you load a sample sheet, as show in the yellow banner at top of thread, with the expected result examples

    Your picture does not support the text

    If Cell A1 = Soon and Cell B1=4, I want it to show in Cell C3 = Medium
    If Cell A1 = Automated and Cell B1=5, I want it to show in Cell C3 = High
    =IF( AND( A1="soon", B1=4) , "Medium", IF( AND( A1="Automated", B1=5), "High", "" ))

    But i'm not sure thats what is needed

    IT looks like form the image - that actually the Cells are
    ROW 2 Urgency are the words
    Column B consequences are the - numbers

    Automatic - 3,4,5 = HIGH - AND( $b4 >=3 , C$2 = " Automatic")
    Automatic - 2,1 = medium

    But before i do much more, can you load the spreadsheet
    Last edited by etaf; 01-19-2021 at 07:56 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    Hi Etaf, Just trying it now. Looks good so far!

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    OK, glad to have helped
    But from the picture - probably need something different

  5. #5
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    slight problem

    it Works, but I get a message "This formula uses more levels of nesting than you can use in the current file format."
    Attached Images Attached Images

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    can you post the formula
    can you upload a spreadsheet sample
    what version of excel , your profile says 2019

  7. #7
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    I was repeating the formula,

    =IF(AND(P8="soon",S8=4),"Medium",IF(AND(P8="Automated",S8=5),"High",IF(AND(P8="Automated",S8=4),"High",IF(AND(P8="Automated",S8=3),"High",IF(AND(P8="Automated",S8=2),"Medium",IF(AND(P8="Automated",S8=1),"Medium"""""""))))))

    Will try and post an edited spreadsheet shortly

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    IF(AND(P8="Automated",S8=5),"High",IF(AND(P8="Automated",S8=4),"High",IF(AND(P8="Automated",S8=3),"High"

    is 5 the highest for 5

    FOR HIGH
    AND(P8="Automated",S8>=3)

    Or if a range of numbers
    AND(P8="Automated",S8>=3, S8<=5)

    As mentioned a spreadsheet example would have helped

    So you could reduce this formula considerably

    For Medium
    IF(AND(P8="Automated",S8=2),"Medium",IF(AND(P8="Automated",S8=1),"Medium
    again is 1 the lowest
    AND(P8="Automated",S8>=2),"Medium"
    Last edited by etaf; 01-19-2021 at 09:30 AM.

  9. #9
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    I have 30 permutations to consider based on the original matrix

    Will look at the suggestions and see how I get on

    Thanks again

  10. #10
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    I have attached the spreadsheet to the original post in this thread

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    thanks , I was in the middle of making one like your picture example and then using a lookup
    BUT
    the spreadsheet is nothing like the picture
    Nothing in row 8 , so cannot even follow the formula example you posted
    and I cant see an explanation of what's required where
    so cant help with that

    can you explain again what you need with reference to the spreadsheet - and ADD the expected results, and perhaps why ?

  12. #12
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    Hi Etaf

    Ah sorry, The original is row 8, the test spreadsheet it's row 6//So Row 6 should be looked at

    The value in P6 is "Automated, Immediate, Prompt, Soon and Event/Log" this is based on the output of O6.

    The Value of S6 is from a dropdown menu for a number between 1 & 5 (Consequence) selected by the user

    What I am trying to get W6 to do is a condition check of the Consequence number S6 (0-5)and the urgency P6 (Automated,Immediate,Prompt, Soon or Event Log" to show "Event", "Low", "Medium" or "High" based on the matrix on the original post

  13. #13
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    this may help, as i had almost finished you picture
    Using a lookup table - although maybe an easier way if we could use numbers in the row 3

    meanwhile here is a spreadsheet
    While i look into your explanation
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    Thanks Etaf, will have a look at the grid matrix

  15. #15
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    Ok, your description and picture still dont match

    In S6 you have 1-5
    In P6 (Automated,Immediate,Prompt, Soon or Event Log)
    Output in W6 ("Event", "Low", "Medium" or "High")

    So a slight modification to my table should work
    BUT I dont know how the above matrix applies , as its not the same

    you could just update the table onto a new sheet
    see matrix sheet on your example
    Attached Files Attached Files

  16. #16
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    Upated the formula for your sample
    BUT used W6-1 to W6-20
    as i dont know the output, for the conditions
    Just change those W6 on the matrix spreadsheet and it will work
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    seem to get #VALUE! message in W6

    How does it handle None, which is 0 to show Event?

  18. #18
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Comparing Text and numbers to output a text

    if that is the default 0 for Event Log in P6 then you can use an IF with the lookup

    =IF( P6="event log", 0, Index......
    Value means it can not find the lookup

    can you post the spreadsheet - BUT WITH THE matrix sheet populated

  19. #19
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    The test Spreadsheet ETAF-1, The formula works. However if I cut it, then paste it back W6 goes to #VALUE

    This is the same if I take the Matrix and formula into the master spreadsheet, same thing.

    Is there something that need to be done to refresh the cell that the formula is in to run it. I did try refresh and that didn't do anything?

    =INDEX(Matrix!C:C,MATCH(S6&P6,Matrix!A:A&Matrix!B:B,0))

  20. #20
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: Comparing Text and numbers to output a text

    The picture tell us: most "high" is in the upper-left, "low" is in lower-right, and Medium is in midle
    So try to assign consequence from 5 to 1 downward, and urgency (minute) from 1 min to 12 hours rightward (like the image attached)
    by using:
    LOOKUP(O6,{0,1,20,60,720}*1.01,{5,4,3,2,1})
    Then multifly them consequence by urgent, the bigest the most ergency:

    W6:
    Please Login or Register  to view this content.
    Attached Images Attached Images
    Attached Files Attached Files
    Quang PT

  21. #21
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    Thanks Bebo

    There is the none part. I have assigned that as 0, so basically anything 0 should read "Event" which isn't on your table.

    There seems to be some issue, if I select Consequence and Prompt the formula shows "high" when it should read "medium"

  22. #22
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: Comparing Text and numbers to output a text

    I believe it should work, could you input the case, highlight the error and post the file again?

  23. #23
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    L6 select 10
    N6 Select 5
    S6 - Select 3

    W8=High when is should be medium
    Attached Files Attached Files

  24. #24
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: Comparing Text and numbers to output a text

    Opp, sorry, a small mistake, replace 12 with 13:

    =LOOKUP(LOOKUP(O6,{0,1,20,60,720}*1.01,{5,4,3,2,1})*S6,{0,5,6,8,13},{"Low","Medium","Low","Medium","High"})

  25. #25
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    Thanks for that. How would it handle a consequence of 0 which I need to show it as Event.

    The calculation assumes 1-5, but should be 0-5, so if consequence 0 (none) then output is "event)

  26. #26
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: Comparing Text and numbers to output a text

    Just using IF to direct into "Event

    =IF(S6=0,"Event",LOOKUP(LOOKUP(O6,{0,1,20,60,720}*1.01,{5,4,3,2,1})*S6,{0,5,6,8,13},{"Low","Medium","Low","Medium","High"}))

  27. #27
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    Thanks

    Applying that , the value stays at "Event" regardless of what S2 is 0-5. Is there something missing?

  28. #28
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: Comparing Text and numbers to output a text

    It refer to S6 (is it consequence?), not S2. If S6=0, definitely yields "Event"

  29. #29
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    Ah yes sorry, if I select S6 as 0 then it should read event. If I change S6 to 1-5, the value stays at event.

  30. #30
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: Comparing Text and numbers to output a text

    It is still in good condition. See attachment again.
    Attached Files Attached Files

  31. #31
    Registered User
    Join Date
    01-19-2021
    Location
    Aberdeen
    MS-Off Ver
    2019
    Posts
    16

    Re: Comparing Text and numbers to output a text

    That seems to work better - Thanks Bebo

+ 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. [SOLVED] Create and output to a text file (test.txt)
    By sirkusjon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2020, 04:57 AM
  2. Separating text from numbers from pdf output
    By Ajmal1000 in forum Excel General
    Replies: 6
    Last Post: 01-10-2015, 04:48 PM
  3. [SOLVED] Comparing two cells for similarities and output a third cell (if C1 = B1, then output A1)
    By PERFECT777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-26-2013, 12:37 AM
  4. [SOLVED] Test if a cell is within a name range and return a text value based on the test
    By DraconR in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-24-2013, 02:46 AM
  5. Clock Time Test for Program with Spreadsheet Output
    By Hillz03 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2012, 08:55 PM
  6. [SOLVED] logical test, array, text and numbers
    By Dan M. in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-25-2006, 04:00 AM
  7. Conditional Test with Text to Speech Output
    By jman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2005, 11:54 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