COUNT two or more consecutive text values and check each if it's +/- than a value

  • Hi,
    I dont know if the topic represents the real thing I want, so here is an explanation...I have these entries in my worksheet:
    [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    Shift table

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [td]

    Th

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [td]

    Su

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [td]

    Th

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [td]

    Su

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [td]

    Th

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [td]

    Su

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [td]

    Th

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [td]

    Su

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [/tr]


    [tr]


    [td]

    Consecutive days of Illness form previous month

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    3

    [/td]


    [td]

    4

    [/td]


    [td]

    5

    [/td]


    [td]

    6

    [/td]


    [td]

    7

    [/td]


    [td]

    8

    [/td]


    [td]

    9

    [/td]


    [td]

    10

    [/td]


    [td]

    11

    [/td]


    [td]

    12

    [/td]


    [td]

    13

    [/td]


    [td]

    14

    [/td]


    [td]

    15

    [/td]


    [td]

    16

    [/td]


    [td]

    17

    [/td]


    [td]

    18

    [/td]


    [td]

    19

    [/td]


    [td]

    20

    [/td]


    [td]

    21

    [/td]


    [td]

    22

    [/td]


    [td]

    23

    [/td]


    [td]

    24

    [/td]


    [td]

    25

    [/td]


    [td]

    26

    [/td]


    [td]

    27

    [/td]


    [td]

    28

    [/td]


    [td]

    29

    [/td]


    [td]

    30

    [/td]


    [td]

    31

    [/td]


    [/tr]


    [tr]


    [td]

    25

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [/TABLE]

    This is a shift table from one employee... ,The upper rows are the days of the month, and down is the row that represents the shifts. Among the regular shifts the employee might be absent because of an illness. The first column represents the days of illness from the previous month (manually entered, if he had any). and with IL are presented the days of illness for the current month. What I want to do in the following table: [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    <=7

    [/td]


    [td]

    40 (expected value from the shift table)

    [/td]


    [/tr]


    [tr]


    [td]

    8-30

    [/td]


    [td]

    0

    [SIZE=13px](expected value from the shift table)[/SIZE]

    [/td]


    [/tr]


    [tr]


    [td]

    31>=

    [/td]


    [td]

    48

    [SIZE=13px](expected value from the shift table)[/SIZE]

    [/td]


    [/tr]


    [/TABLE]

    Is to count the different periods of illness. check if every of them are less then 7, between 8 and 30 or 31+ consecutive days, multiply by 8 (as a regular working day) and place them into the right column respectively. When multiplying as a regular working days it should count and multiply only if it is a workday, but not weekends (Saturday and Sunday). This is necessary because different period of illness is paid differently (from the company, from the health insurance etc.)
    I tried to do something by using Frequency function, but I stuck, searched thru internet for a solution, and could find any,,,,
    Hope someone has it....
    Thanks in advance


    Regards
    Aleksandar

  • Hi,


    In order to make things easier for any contributor who would like to help you out ... I would suggest to attach your worksheet with your next message ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Thanks for your example file ... :smile:


    Are you after a solution without the calculations in Rows 4,5,6,7 ...?


    Can you explain the reasons why the first illness period falls into your third category ... and the second illness period into your first category ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    Solution without rows 4,5,6,7 would be the perfect one... :grin:
    The first illness period falls into third category because in the previous month that person had illness for 25 consecutive days (Cell B3), and it continued into this, current, month. That way it is a 33 consecutive days of illness which fall to the third category (more than 30 days of illness). This category of illness is covered by the health insurance. So the employee will get 100% payment for those hours from the health insurance. The second period is six consecutive days (5 without weekends) and those 40 hours are paid 70% by the company as a category for up to seven consecutive days.
    The complicated thing here (in our country) is that if you want to determinate the category of illness you are taking in consideration the consecutive days of absence, but among those sets/periods you are calculating only the working days as hours (regular 8 hour workday) that should be paid (either from the company or the health insurance institution).


    BTW I used rows 4,5 6,7 because I am not an excel expert, so that way, in order to get a solution, I need two or three more steps to get it :D


    Hope this was helpful!


    Cheers

  • Hi,


    Thanks a lot for your additional explanations ...which are very clear ... :wink:


    As soon as I have a moment ... I will dig into your example ...and look for a solution ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Took some time to review your worksheet ... Interesting problem ...


    Could you explain your formulas in cells B10,B11,B12 ... since they are all using row 5 ... which is about to be deleted...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,


    Indeed an interesting problem, as I was searching all over internet to find something similar, but always ended up in solutions that determine the unique values, the frequency, max consecutive values and so on.... but none of them splitting the same values into consecutive sets of values, execute them separately from the array and count them by a condition or two as I need... :hubbahub:
    The formulas in cells B10, B11 and B12 are actually checking if the consecutive days are smaller or equal to 7, between 8 and 30 or larger than 31, accordingly. I am not 100% sure if the formulas are correct, but the point is that they are checking if the first day of the month is illness, If so, it is logically that the illness period is continuing from the last month (which does not have to be of course....), afterwards it checks the condition if value in B3 plus the first set of consecutive values of the array is smaller, between or larger, if true it sum cell B3 and the first set of consecutive values of the array plus all values of the array with the condition (<7 8-30, >30), minus the first value of the array because it is already calculated (I figured out that I have made some omissions in the formula, attached is the updated formulas for that cells). The false values are checking only the conditions and sum the values in cells accordingly.
    Row 4 and 5 are actually the same thing with that difference that I needed only the highest, end, values of the consecutive sets of the array... If I execute this formula into the fourth row it would calculate everything smaller then 7 (1, 2 3, 4 ,5 etc...)
    If it is not possible to have a solution with only one formula in cell, I dont mind using this type, because everything will be working in background in separated sheet, I will read only the final values from C10 to C12 in another sheet (or workbook).... and here I really don't have an idea how to make a formula to put values from row 7 as they are put from row 5 into C10 to C12 accordingly.... :crying:

  • Hello,


    Thanks a lot for all your additional explanations ...:wink:


    My understanding is that the ' B10 -B12 ' recap can be considered as the conclusion of all your calculations ...


    Meanwhile, let me share with you the current status ...


    Attached is a file test Version 1 ...


    Could you kindly review it ...test it .. and share your comments ... :wink:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,


    Thanks a lot for the attachment


    I was testing it today, and found two "bugs"... I re-attached the file with my remarks (test 1 worksheet for the first remark and test 2 worksheet for the second remark).


    Namely, half of the day I was trying to find a solutions.... And I thing I found for the first thing (related to the final hours of illness.... if you have two identical periods of illness, with the index/match function, you are getting only the firs row with the values, the others are omitted). Please check if it is ok? :)


    Second one is explained in the worksheet (test 2)


    I will continue to test it, and make a deeper review as I can understand what are all functions and formulas doing :D


    [USER="31712"]Carim[/USER]

    [SIZE=13px] [/SIZE]

    ,thank you very much for your time and effort. I think we have 99,99% of the solution

  • Hello,


    Thanks for all you tests ... :wink:


    Will take a look at the three bugs ...


    Before getting into your Recap table B10 :D12 ... will dive into the main two bugs ...


    The exact number of illness periods ... under all circumstances ... is crucial ...!!!


    ( The frequency function does not handle the whole 31 days with IL ...)


    The calendar issue is related to the size of all the arrays ... and should not be a difficult issue ...


    Will get back to you asap ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Aleksandar


    Just made a couple of changes to account for :


    1. The whole month ... with all days showing 'IL'


    2. The calendar adjustment in cell AI2 ...


    Once all the hurdles in the basic calculations are cleared ... in range B14:M31 ...


    we will take a look at your Recap table B10 :D12 ...:wink:


    Let me have your comments ...:wink:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,


    Regarding the 31 days (or whole month) of illness, will the following add to the formula work in C17?
    {=IF($C17="";"";INDEX(IF(SUM(FREQUENCY(IF(($C$3:$AG$3="IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3));IF(($C$3:$AG$3<>"IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3))))=DAYS((EOMONTH($AI$2;0));$C$2)+1;SUM(FREQUENCY(IF(($C$3:$AG$3="IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3));IF(($C$3:$AG$3<>"IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3))));FREQUENCY(IF(($C$3:$AG$3="IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3));IF(($C$3:$AG$3<>"IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3))));SMALL…..


    :smile:

  • Hi,


    If we were to zoom into the whole month of illness ...


    In this case, the frequency function generates two elements 0 and 31 (or more precisely the total numbers of days ...)


    On the other side, the small function generates 1 ... since it is picking the smallest of the two equal counts : 1 and 1 ...


    So with the tiny modification of adding 1 to the C17 instance of 1 ... the frequency function picks the second element ...i.e. the total number of days ...


    which is exactly what you need ... in this very specific case of ' the whole month of illness' ...


    Hope the explanation is clear enough ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim


    All my remarks are into the workbook attachment.


    The "problem" that I found regarding the calculation of workdays is in worksheet {test 3}, In

    [SIZE=13px]worksheet {test 4} after longer review of the formula I thing I managed to solve it, but please take a review on it, and confirm that you have the solution for this post? :wowee:


    In worksheet {test} there is also small remark, it is explained inside. Let me know if you agree with it....[/SIZE]


    Million thanks for you time as well as sharing you knowledge and expertise!!! You are awesome!

    [SIZE=13px]:pirate:[/SIZE]

  • Hello Aleksandar,


    Fully agree with your remark about the first worksheet for the first category in Recap table ... :rock:


    Besides ... you will have to give me the name of these two strange diseases : the Week-End Spleen and the Every-Other-Day Syndrome ... :smash:


    More seriously ... as soon as I have a moment ... I will take a look at both these cases ....:rambo:



    Edit : The fact that the number of working days is by definition not equal to the number of the calendar days ... disrupts the list of illness periods ...
    As a consequence ... the working days are out of line ... and practically .. we will have to design another approach for the working days ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    Attached is a modification for the working days ... version 1.3


    Hopefully ... we are getting closer to handling ALL illness cases ... :drunk:


    Let me have your comments ...:wink:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • [USER="31712"]Carim[/USER] I think we can mark this thread as {SOLVED} ( https://www.ozgrid.com/forum/f…lue?p=1207546#post1207546 ) as this is what I've been looking for... (Cannot find option to mark this thread as solved, neither an option to edit the first post and add "solved") :wowee: :party:


    Thank you very much for all you work, you are incredible!!! :thanx: :yourock:


    Hopefully, this will be a solution to someone's else problems in future :smile:


    Cheers! :drink: