Create a formula to get the result

[Maddy]

Hi There,

In my attached sheet I am looking to find a formula where I can get the result of HD if its greater than or equal to 3=0. However,i have defined HD =0.5. but if somebody takes 3 consecutive HD it should be counted 0 .
I hve tried with formula sumproduct(sumif) but unable to get result. Please guide me to understand.

Thanks,
Madhoor

 

[GraH - Guido]

Hi,
The values for the codes F, A, HD are 1 , 2 , 0.5. Except when you 3 consecutive HD values in a row, it should be 0? Not consecutive would be 1.5 then?

 

[Maddy]

Hello GraH,
Values are 1,0.5, and 0...I think my question is a bit wrong.. if there are 3 consecutive HD it should convert into A ( only value of A ).can it be possible.

 

[GraH - Guido]

Perhaps it's me, I don't think the rules are clear.
F = 1
A = 0.5
HD = 0
HD-HD-HD = A = 0.5, or only if A is present?

If a patterns is :

• F A F F A the total is 1 + 0.5 + 1 + 1 + 0.5 = 4?
• HD-A-HD-HD-F = 0 + 0.5 + 0 + 0 + 1 = 1.5?

I think it is possible, not sure I'm able to come up with a single formula, though. But at least I can try when the pattern is made clear.

 

[Maddy]

Dear,
per requirement values are
F(Full Day)=1
HD(HalfDay)=0.5
A(Absent)=0
and next condition is if there are 3 consecutive HD then value should be 0.

like in sheet for week 5 - Mon,Tue, and Wed 3 consecutive HD is taken. Its value should be equal to 0.

Hope this is made clear to you.

Thanks.

 

[GraH - Guido]

Hi, it does make things clearer. I'll take a further look later today/this week.

 

[bosco_yip]

Dear,
per requirement values are
F(Full Day)=1
HD(HalfDay)=0.5
A(Absent)=0
and next condition is if there are 3 consecutive HD then value should be 0.

like in sheet for week 5 - Mon,Tue, and Wed 3 consecutive HD is taken. Its value should be equal to 0.

Hope this is made clear to you.

Thanks.

Try,



1] Keep Column G in blank

2] In H2, array formula (CTRL+SHIFT+ENTER) copied down :

=SUM(SUMIF(J$3:J$5,B2:F2,K$3:K$5)*IF(MAX(FREQUENCY(IF(B2:F2="HD",COLUMN(B2:F2)),IF(B2:F2<>"HD",COLUMN(B2:F2))))>=3,((B2:F2=A2:E2)+(B2:F2=C2:G2))=0,1))

p.s. for HD >=3 consecutives, that cell value =0 (see example week 5,6,7 and 8)

Regards
Bosco

 

[GraH - Guido]

A challenge for me = a walk in the park for @bosco_yip .
Yet another formula to study a bit (lot?).