Results 1 to 6 of 6

Thread: Managing blank values in dataset

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Managing blank values in dataset

    Hello,

    I have a dataset corresponding to snowfall sum per day, for a number of years (30+). There are often blanks for certain days. Where there is a blank day in a month, I wish to set the entire month (every day in the month) a value M (for missing) or set the entire month blank, either is fine. The data looks as such, days and years are received sequentially. Any ideas on where to start?

    Month Day Year Snowfall
    12 1 2010 0
    12 2 2010 0
    12 3 2010 0
    12 4 2010 0
    12 5 2010 0
    12 6 2010 0
    12 7 2010 0
    12 8 2010 0
    12 9 2010 0
    12 10 2010 0
    12 11 2010 0.2
    12 12 2010 0
    12 13 2010 0
    12 14 2010 0
    12 15 2010 0
    12 16 2010 0
    12 17 2010 0
    12 18 2010 0
    12 19 2010 0
    12 20 2010 0
    12 21 2010 0
    12 22 2010 0
    12 23 2010 0
    12 24 2010 0
    12 25 2010 0
    12 26 2010 0
    12 27 2010 4
    12 28 2010 2.2
    12 29 2010 0
    12 30 2010 0
    12 31 2010 0
    1 1 2011
    1 2 2011
    1 3 2011
    1 4 2011
    1 5 2011
    1 6 2011
    1 7 2011
    1 8 2011 1.5
    1 9 2011 1
    1 10 2011
    1 11 2011
    1 12 2011 9
    1 13 2011 4.5
    1 14 2011
    1 15 2011
    1 16 2011 0.4
    1 17 2011
    1 18 2011 1.8
    1 19 2011 3
    1 20 2011 0
    1 21 2011 4.5
    1 22 2011 0
    1 23 2011
    1 24 2011
    1 25 2011 1.5
    1 26 2011 0.3
    1 27 2011 4
    1 28 2011
    1 29 2011 0.2
    1 30 2011
    1 31 2011

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Managing blank values in dataset

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    1
    Month Day Year Snowfall Result formula in E2 copied down
    2
    12
    1
    2010
    0
    0
    =IF(COUNTIFS(A:A,A2,C:C,C2,D:D,"")>0,"M",D2)
    3
    12
    2
    2010
    0
    0
    4
    12
    3
    2010
    0
    0
    5
    12
    4
    2010
    0
    0
    6
    12
    5
    2010
    0
    0
    7
    12
    6
    2010
    0
    0
    8
    12
    7
    2010
    0
    0
    9
    12
    8
    2010
    0
    0
    10
    12
    9
    2010
    0
    0
    11
    12
    10
    2010
    0
    0
    12
    12
    11
    2010
    0.2
    0.2
    13
    12
    12
    2010
    0
    0
    14
    12
    13
    2010
    0
    0
    15
    12
    14
    2010
    0
    0
    16
    12
    15
    2010
    0
    0
    17
    12
    16
    2010
    0
    0
    18
    12
    17
    2010
    0
    0
    19
    12
    18
    2010
    0
    0
    20
    12
    19
    2010
    0
    0
    21
    12
    20
    2010
    0
    0
    22
    12
    21
    2010
    0
    0
    23
    12
    22
    2010
    0
    0
    24
    12
    23
    2010
    0
    0
    25
    12
    24
    2010
    0
    0
    26
    12
    25
    2010
    0
    0
    27
    12
    26
    2010
    0
    0
    28
    12
    27
    2010
    4
    4
    29
    12
    28
    2010
    2.2
    2.2
    30
    12
    29
    2010
    0
    0
    31
    12
    30
    2010
    0
    0
    32
    12
    31
    2010
    0
    0
    33
    1
    1
    2011
    M
    34
    1
    2
    2011
    M
    35
    1
    3
    2011
    M
    36
    1
    4
    2011
    M
    37
    1
    5
    2011
    M
    38
    1
    6
    2011
    M
    39
    1
    7
    2011
    M
    40
    1
    8
    2011
    1.5
    M
    41
    1
    9
    2011
    1
    M
    42
    1
    10
    2011
    M
    43
    1
    11
    2011
    M
    44
    1
    12
    2011
    9
    M
    45
    1
    13
    2011
    4.5
    M
    46
    1
    14
    2011
    M
    47
    1
    15
    2011
    M
    48
    1
    16
    2011
    0.4
    M
    49
    1
    17
    2011
    M
    50
    1
    18
    2011
    1.8
    M
    51
    1
    19
    2011
    3
    M
    52
    1
    20
    2011
    0
    M
    53
    1
    21
    2011
    4.5
    M
    54
    1
    22
    2011
    0
    M
    55
    1
    23
    2011
    M
    56
    1
    24
    2011
    M
    57
    1
    25
    2011
    1.5
    M
    58
    1
    26
    2011
    0.3
    M
    59
    1
    27
    2011
    4
    M
    60
    1
    28
    2011
    M
    61
    1
    29
    2011
    0.2
    M
    62
    1
    30
    2011
    M
    63
    1
    31
    2011
    M
    Sheet: Sheet1

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Managing blank values in dataset

    Quote Originally Posted by transgenesis View Post
    I have a dataset corresponding to snowfall sum per day, for a number of years (30+).
    Whilst the previous suggestion will work, if your data set is 30 years or more of daily data, the processing required by it is much larger than required. For 30 years of daily data it took 150 seconds on my machine. For the same data, this version took less than 0.02 seconds & produced the same results.

    Formula in E2, copied down.

    =IF(A2=A1,IF(E1="M","M",D2),IF(COUNTIFS(A2:A32,A2,D2:D32,""),"M",D2))
    Last edited by Peter_SSs; Oct 9th, 2019 at 01:27 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    New Member
    Join Date
    May 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Managing blank values in dataset

    Thank you both for the quick reply, both options work, and are exactly what I was looking to do. Appreciate your time.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Managing blank values in dataset

    Glad you are sorted

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Managing blank values in dataset

    You're welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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