Results 1 to 7 of 7

Thread: MS Excel IF statement with multiple conditions

  1. #1
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default MS Excel IF statement with multiple conditions

    Hi can some help me create formula for Excel spreadsheet so if
    Input value is 15
    Output value ?


    Range Value
    0... 9 = 0
    10 19 = 1
    20 29 = 2
    30 39 = 3
    40 49 = 4
    50 59 = 5
    60 69 = 6
    70 79 = 7
    80 89 = 8
    90 99 = 9

    Thanks
    Last edited by roma; 05-04-22 at 04:38 AM.



Look Here ->
  • #2
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    You question makes no sense? Are you trying to say if range is between 10 and 19 then the response would be 1? i.e. if 15 > 10 and 15 <20 then result = 1
    I'm out of my mind, but feel free to leave a message...

  • #3
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default

    Hi Isemmes,

    Using the data from the Value ( 0 to 99 ).
    Correct Output Value is outputted as a result of the Input Value

    If the Input Value is 5 then the the Output Value is 0
    If the Input Value is 21 then the the Output Value is 2
    If the Input Value is 65 then the the Output Value is 6
    If the Input Value is 93 then the the Output Value is 9
    and so on.

    This is a sample data of numbers what I need

    I don't know how to upload sample xlxs file of what I need to achieve Sorry.

    Last edited by roma; 05-04-22 at 01:41 PM.

  • #4
    Junior Member
    Join Date
    Jan 2008
    Posts
    30
    Thanks
    10
    Thanked 12 Times in 9 Posts
    Rep Power
    202
    Reputation
    197

    Default

    A little bit of cheating... however...

    Range 1 Range 2 Value Helper Column Input 601
    654 660 0 =+IF(AND((B5<=$G$4),($G$4<=C5)),0,"") Output =+SUM(E5:E14)
    648 653.9 1 =+IF(AND((B6<=$G$4),($G$4<=C6)),1,"")
    642 647.9 2 =+IF(AND((B7<=$G$4),($G$4<=C7)),2,"")
    636 641.9 3 =+IF(AND((B8<=$G$4),($G$4<=C8)),3,"")
    630 635.9 4 =+IF(AND((B9<=$G$4),($G$4<=C9)),4,"")
    624 629.9 5 =+IF(AND((B10<=$G$4),($G$4<=C10)),5,"")
    618 623.9 6 =+IF(AND((B11<=$G$4),($G$4<=C11)),6,"")
    612 617.9 7 =+IF(AND((B12<=$G$4),($G$4<=C12)),7,"")
    606 611.9 8 =+IF(AND((B13<=$G$4),($G$4<=C13)),8,"")
    600 605.9 9 =+IF(AND((B14<=$G$4),($G$4<=C14)),9,"")

    You might need to double the check the less than or equal to ensure correct results on the boundary !
    Attached Images Attached Images
    Last edited by jglnb; 05-04-22 at 05:55 PM.

  • The Following User Says Thank You to jglnb For This Useful Post:

    roma (06-04-22)

  • #5
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    That might work, provided that Range1 And Range2 are constant. If either of those values changes then you may get multiple results in the Helper column which would then translate to the sum total of those values in the output. A might be better. I'm still attempting to understand exactly what it is the OP is trying to achieve. Is this an assignment? Or is there a real world need?
    I'm out of my mind, but feel free to leave a message...

  • #6
    Junior Member
    Join Date
    Jan 2008
    Posts
    30
    Thanks
    10
    Thanked 12 Times in 9 Posts
    Rep Power
    202
    Reputation
    197

    Default

    Agreed - the variability is an issue. Personally, I would whip up something super simple in QB64 !!!

  • #7
    Junior Member roma's Avatar
    Join Date
    Dec 2008
    Posts
    91
    Thanks
    9
    Thanked 1 Time in 1 Post
    Rep Power
    189
    Reputation
    12

    Default

    Thanks Jglnb, that has worked problem solved 🙂👍

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