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

[aspasovski86]

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="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/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<strike></strike>
[/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]
</tbody>[/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="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]<=7<strike></strike>
[/TD]
[TD]<strike></strike>40 (expected value from the shift table)<strike></strike>
[/TD]
[/TR]
[TR]
[TD]8-30<strike></strike>
[/TD]
[TD]<strike></strike>0 (expected value from the shift table)<strike></strike>
[/TD]
[/TR]
[TR]
[TD]31>=<strike></strike>
[/TD]
[TD]<strike></strike>48 (expected value from the shift table)<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>





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

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...alues-and-check-each-if-its-than-a-value.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[aspasovski86]

Thanks to a hard work from Mr. Carim, we have a solution for this thread

In the link below, you can find the solution

https://www.ozgrid.com/forum/forum/...ch-if-it-s-than-a-value?p=1207546#post1207546

Cheers to everyone!