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.

  1. Maddy

    Maddy New Member

    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,
    Madhoor

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    798
    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?
  3. Maddy

    Maddy New Member

    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 - Guido

    GraH - Guido Well-Known Member

    Messages:
    798
    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.
  5. Maddy

    Maddy New Member

    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 - Guido

    GraH - Guido Well-Known Member

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

    bosco_yip Excel Ninja

    Messages:
    1,950
    Try,

    upload_2018-5-16_22-51-14.png

    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:

    Last edited: May 16, 2018
    GraH - Guido likes this.
  8. GraH - Guido

    GraH - Guido Well-Known Member

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

Share This Page