1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# Create a formula to get the result

Discussion in 'Ask an Excel Question' started by Maddy, May 14, 2018.

Messages:
11
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,

File size:
9.8 KB
Views:
11
2. ### GraH - GuidoWell-Known Member

Messages:
905
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?

Messages:
11
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.
4. ### GraH - GuidoWell-Known Member

Messages:
905
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.

Messages:
11
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.
6. ### GraH - GuidoWell-Known Member

Messages:
905
Hi, it does make things clearer. I'll take a further look later today/this week.
7. ### bosco_yipExcel Ninja

Messages:
2,105
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

#### Attached Files:

• ###### consecutive HD.xlsx
File size:
12.1 KB
Views:
2
Last edited: May 16, 2018
GraH - Guido likes this.
8. ### GraH - GuidoWell-Known Member

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