+ Reply to Thread
Results 1 to 17 of 17

how to teach a pattern in excel

  1. #1
    Forum Contributor
    Join Date
    12-20-2016
    Location
    Uzbekistan
    MS-Off Ver
    2016
    Posts
    189

    Question how to teach a pattern in excel

    Dear Excel Professionals,

    Let me ask You a question, could you please help me to make a pattern thereis a data as temperature, it plays between 0 and 100, when number increases between 30 and 70 = result should be "low", when data decreases from 100 to = result should be "high". Attaching screen and file.

    data pattern.jpg

    Thank You
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: how to teach a pattern in excel

    Please try at F2 and copy down

    =IF((E2>70)+(E2>E3),"high","low")

  3. #3
    Forum Contributor
    Join Date
    12-20-2016
    Location
    Uzbekistan
    MS-Off Ver
    2016
    Posts
    189

    Re: how to teach a pattern in excel

    a bit small mistakes in formula could we fix?patter graph 2.jpg

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: how to teach a pattern in excel

    E12:E13 35 44 are "low" after 30 why
    E24:E25 31 35 are "high" after 30 ?

  5. #5
    Forum Contributor
    Join Date
    12-20-2016
    Location
    Uzbekistan
    MS-Off Ver
    2016
    Posts
    189

    Re: how to teach a pattern in excel

    Yes, E12:E13 low because it is lower than 30 look at graph to understand easier, it seem it is complex situation
    and E24:E25 are higher than 30 (graph increasing in trend)

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to teach a pattern in excel

    Attach the workbook, not a picture of it. It's too small to read, anyway.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: how to teach a pattern in excel

    Sorry, I don't get it.
    E12:E13 35 and 44 are higher than 30

    11:13 and 23:25 are the same situation to me.

    tta.jpg

  8. #8
    Forum Contributor
    Join Date
    12-20-2016
    Location
    Uzbekistan
    MS-Off Ver
    2016
    Posts
    189

    Re: how to teach a pattern in excel

    AliGW Done
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    12-20-2016
    Location
    Uzbekistan
    MS-Off Ver
    2016
    Posts
    189

    Re: how to teach a pattern in excel

    Bo_Ry, here is a pic to understand the logic behind it, sorry for my graphing skills Attachment 603618
    Attached Images Attached Images
    Last edited by tta.akmal; 12-27-2018 at 10:29 AM.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: how to teach a pattern in excel

    Try this

    =IF(And(E2>E1,E2>70),"high",If(and(E2<E1,E2<30),"low"))
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  11. #11
    Forum Contributor
    Join Date
    12-20-2016
    Location
    Uzbekistan
    MS-Off Ver
    2016
    Posts
    189

    Re: how to teach a pattern in excel

    I tried now first value is "high" then it worked FALSE, small error here
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to teach a pattern in excel

    Try this:

    =IF(AND(E2<E1,E2>30),"High",IF(AND(E2<E1,E2<30),"Low",IF(AND(E2>E1,E2>70),"High","Low")))

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: how to teach a pattern in excel

    Revised formula.
    In F2 then copied down.

    =IF(E2>E1,IF(E2>70,"high","low"),IF(E2<E1,IF(E2<30,"low","high")))
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    12-20-2016
    Location
    Uzbekistan
    MS-Off Ver
    2016
    Posts
    189

    Re: how to teach a pattern in excel

    AliGW, formula worked pretty well, till F56:F59 and etc. i formatted with conditional format, can you check?
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    12-20-2016
    Location
    Uzbekistan
    MS-Off Ver
    2016
    Posts
    189

    Re: how to teach a pattern in excel

    kvsrinivasamurthy, formula worked pretty good till F50 (error FALSE) and as in AliGW
    Attached Files Attached Files
    Last edited by tta.akmal; 12-29-2018 at 05:12 AM. Reason: grammar

  16. #16
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: how to teach a pattern in excel

    This is base on image from Post#9

    F2
    =IF(E2<30,"low",IF(E2>70,"high",F1))

  17. #17
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: how to teach a pattern in excel

    In F2

    =IF(E2>E1,IF(E2>70,"high","low"),IF(E2<E1,IF(E2<30,"low","high"),F1))

+ 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. Teach me some Excel Pro Formulas.
    By Crszwsky in forum Excel General
    Replies: 1
    Last Post: 02-23-2018, 06:05 AM
  2. [SOLVED] I need to teach Excel a desired span when automatically filling in formulas
    By j1821282 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-06-2016, 05:51 PM
  3. [SOLVED] Lookup and return rows based on pattern start and pattern end
    By JDI in forum Excel General
    Replies: 18
    Last Post: 11-16-2014, 11:44 PM
  4. Replies: 1
    Last Post: 02-07-2014, 01:27 AM
  5. If you to teach Excel 101/102 to colleagues...
    By ian128 in forum The Water Cooler
    Replies: 4
    Last Post: 08-15-2013, 03:07 PM
  6. Suggestions to impress my teach on my Excel project
    By AStudent in forum Excel General
    Replies: 1
    Last Post: 11-20-2012, 02:11 AM
  7. Replies: 1
    Last Post: 05-10-2009, 03:20 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