Results 1 to 4 of 4

Thread: Urgent, can someone help for this formula

  1. #1

    Urgent, can someone help for this formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have this file and need to count the continuous date value (=1)
    I already made for each month but cannot make for several months.
    Now I need to help: how to make formula for B70 have the result as the same B71
    Can someone help for this file, thanks you.

    Caculation the continuous date value.xlsx

    Sorry for my bad explain :-)

  2. #2

    Array formula? need to help

    Could someone please help to solve this issue (please note that file's format could not be broke for some reason). Thanks
    Last edited by linh091; 2013-08-18 at 04:24 PM. Reason: due to bad explain

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Array enter this formula into B70, then copy across.
    =MAX(MAX(FREQUENCY(IF(B2:B32=1,ROW(B2:B32)),IF(B2:B32=0,ROW(B2:B32)))),MAX(FREQUENCY(IF(B37:B65=1,ROW(B37:B65)),IF(B37:B65=0,ROW(B37:B65)))),MAX(ROW(B2:B32))-MAX((B2:B32=0)*ROW(B2:B32))+MATCH(0,B37:B65,0)-1)

    See the attached worksheet.Caculation the continuous date value.xlsx

  4. #4

    Quote Originally Posted by JeffreyWeir View Post
    Array enter this formula into B70, then copy across.
    =MAX(MAX(FREQUENCY(IF(B2:B32=1,ROW(B2:B32)),IF(B2:B32=0,ROW(B2:B32)))),MAX(FREQUENCY(IF(B37:B65=1,ROW(B37:B65)),IF(B37:B65=0,ROW(B37:B65)))),MAX(ROW(B2:B32))-MAX((B2:B32=0)*ROW(B2:B32))+MATCH(0,B37:B65,0)-1)
    Thank you for your help, JeffreyWeir. It will help me to save a lot of time on the job.

    However, I have tried for some other situations and find several errors may occur (see attached file):
    1. If there is not any 0 on the first month, the result will be incorrect
    2. If there is not any 0 in the second month, the result is #N/A

    Btw, could you tell me how the formula is if there are more than 2 months in the sheet

    Again, thank you so much for your kind support
    Attached Files Attached Files
    Last edited by linh091; 2013-08-20 at 09:04 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •