Managing blank values in dataset

transgenesis

New Member
Joined
May 11, 2018
Messages
3
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?

MonthDayYearSnowfall
12120100
12220100
12320100
12420100
12520100
12620100
12720100
12820100
12920100
121020100
121120100.2
121220100
121320100
121420100
121520100
121620100
121720100
121820100
121920100
122020100
122120100
122220100
122320100
122420100
122520100
122620100
122720104
122820102.2
122920100
123020100
123120100
112011
122011
132011
142011
152011
162011
172011
1820111.5
1920111
1102011
1112011
11220119
11320114.5
1142011
1152011
11620110.4
1172011
11820111.8
11920113
12020110
12120114.5
12220110
1232011
1242011
12520111.5
12620110.3
12720114
1282011
12920110.2
1302011
1312011

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
1
MonthDayYearSnowfallResult 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
 
Upvote 0
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:
Upvote 0
Thank you both for the quick reply, both options work, and are exactly what I was looking to do. Appreciate your time.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top