Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Counting of smiliar type of data based on certain conditions

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counting of smiliar type of data based on certain conditions

    Hi All

    I have a set of data in a single column stated below :

    30.32
    73.82
    133.99
    51.93
    51.77
    54.55
    55.24
    60.95
    60.95
    60.95
    52.35
    Now if i want to get the final result where all rows ending with .95 should be true or else false, how can i derive through simple excel formula.

    Thanks in advance in anticipation to get positive response.

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,589
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    OMKARTHEPOWER,

    How about this?

    Excel 2007
    AB
    130.32False
    273.82False
    3133.99False
    451.93False
    551.77False
    654.55False
    755.24False
    860.95True
    960.95True
    1060.95True
    1152.35False

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=IF(RIGHT(A1,2)="95","True","False")

    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  3. #3
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,565
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    Another possibility......
    Excel 2007
    AB
    130.32FALSE
    273.82FALSE
    3133.99FALSE
    451.93FALSE
    551.77FALSE
    654.55FALSE
    755.24FALSE
    860.95TRUE
    960.95TRUE
    1060.95TRUE
    1152.35FALSE

    Sheet7



    Worksheet Formulas
    CellFormula
    B1=TRUNC(A1-INT(A1),2) =0.95

    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  4. #4
    New Member
    Join Date
    Nov 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    Thanks a lot for the resolution.

    one more query .. if I add another condition to it, it shows error.

    If I say ending with .95 or .24,

    then i suppose formula should be

    =IF(RIGHT(A1,2)=or("95","24"),"True","False")

    But it is showing error.

    Can you please help guide me on the same.

    Thanks
    Anand

  5. #5
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,565
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    Anand,
    As hiker95 would appear to be offline, I'm sure he won't mind me answering.

    Your use of OR needs to be.....

    =IF(OR(RIGHT(A1,2)="95",RIGHT(A1,2)="24"),"True","False") for hiker's

    or =OR(TRUNC(A1-INT(A1),2) =0.95,TRUNC(A1-INT(A1),2) =0.24) for mine.

    Hope that helps.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  6. #6
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,589
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    Snakehips,

    You beat me to it - thanks.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  7. #7
    New Member
    Join Date
    Nov 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    Thanks a lot Tony for helping me to resolve this issue.

  8. #8
    New Member
    Join Date
    Nov 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    Now if i wish to add certain conditions .. please refer to column below:

    126.64
    118.64
    413.92
    13.39
    58.39
    58.39
    13.39
    58.39
    206.09
    226.09
    206.09
    23.28
    171.51
    206.09
    171.51
    171.51
    206.09
    206.09
    171.54
    224.38
    224.38
    224.38


    Now if i want to get the final result stated with conditions as below :

    1) Any figure after decimal which is divisible by 9 should be true( for e.g. .09,.18,.27,.36,.45,.54. and so on) or

    2) Any figure before decimal which which ends with 13 or its multiple (to a maximum of two digit result.. i.e. to say till 91 as 104 becomes three digit result) should be true. Rest all shopuld be false.

    Thanks in advance.

  9. #9
    New Member
    Join Date
    Nov 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    Is there anyone who can help me out please?

    Thanks in advance.

    Regards
    Anand

  10. #10
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,565
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Counting of smiliar type of data based on certain conditions

    Anand,

    Try...Excel 2007
    ABC
    1126.64FALSEFALSE
    2118.64FALSEFALSE
    3413.92FALSEFALSE
    413.39FALSETRUE
    558.39FALSEFALSE
    658.39FALSEFALSE
    713.39FALSETRUE
    858.39FALSEFALSE
    9206.09TRUEFALSE
    10226.09TRUEFALSE
    11206.09TRUEFALSE
    1223.28FALSEFALSE
    13171.51FALSEFALSE
    14206.09TRUEFALSE
    15171.51FALSEFALSE
    1652.51FALSETRUE
    17206.09TRUEFALSE
    18206.09TRUEFALSE
    19171.54TRUEFALSE
    2078.63TRUETRUE
    21224.81TRUEFALSE
    22224.38FALSEFALSE

    Sheet9



    Worksheet Formulas
    CellFormula
    B1=MOD(ROUND(A1-INT(A1),2)*100,9)=0
    C1=MOD(INT(A1),13)=0

    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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