number of consecutive lengths in a month of all year
number of consecutive lengths in a month of all year
number of consecutive lengths in a month of all year
hi
i have daily rainfall data in excel (column A=year, B=month, C=rainfall amount in mm).
i want to calculate number of times the rainfall value is consecutively zero, that is if the value is zero for consecutively 2 or 3 days they should be considered as one, and and number of consecutive times values are zero to be calculated monthly wise (all the year January month .......)
please help me i have attached the data file also.
Not sure why you have added a Poll to your thread - it wasn't there when I first looked.
Your data goes up to 1980 - are you likely to add any more data to this?
How do you envisage your results to look? It would be helpful if you submitted an example of (made up) results.
Pete
Re: number of consecutive lengths in a month of all year
Dear Pete_UK
i thank you for reply and showing interest to help me in solving the problem.
yes i have to add some more data to this,
and again i have attached a file with results of January month please have a look
in example file 5 number of rainy days indicate only five days rain occurred in jan month including all the years, 3 number of wet spell indicate there consecutive events (one one day event in 1971, two two day events one in 1975 another in 1978,
since number of rain events are less in jan month i counted them using filter option, i have to do that for dry spell(non rainy days or zero value) also, it will be helpful if i can get formulas atleastfor first two columns
thank you.
Just to let you know that I have been working on this (but I've also been doing a lot of other things today). I was wondering how you want to treat things if you have a wet spell that begins towards the end of a month and continues into the next month. One example of this is in June 1972 where there are 7 continuous wet days that finish in July 1972 (6 in June, 1 in July), and again in October 1972 there are 4 days of rain and one day in November (5 days total). It isn't always one day in the next month - in September 1973 there are 8 days of continuous rainfall with 4 days in Sept and 4 days in October.
Do you want to split the days into the different months?
If so, what happens if there is only one day in one of the months - does this not get counted in your column for number of consecutive events?
There are 18 of these split-month wet spells, out of 358 wet spells in your data.
Pete
Been working on this as well.
While waiting for answers to Pete's questions went ahead an posted what I think you mean.
In G2 array-enter this formula and fill down.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
Then array-enter this formula in H2 and fill down.Then in I2 array enter and fill down and across as far as you feel necessary.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Dave
I approached this in a different way. First of all, I put this formula in D1:
=COUNTA(A:A)
which tells me how many rows are used in the data (assuming it is contiguous, i.e. no blank rows). Then I put these array* formulae in the cells stated:
F2: =IF(COUNTIF(INDIRECT("C2:C"&$D$1),"0"),IF(C2=0,2,MIN(IF((INDIRECT("c2:C"&$D$1)=0)*(INDIRECT("C1:C"&$D$1-1)=0),ROW(INDIRECT("A2:A"&$D$1))))),"")
F3: =IF(OR(G2="",G2>=$D$1),"",IF(COUNTIF(INDIRECT("c"&G2+1&":c"&$D$1),"0"),MIN(IF((INDIRECT("c"&G2+1&":c"&$D$1)=0)*(INDIRECT("c"&G2&":c"&$D$1-1)>0),ROW(INDIRECT("A"&G2+1&":A"&$D$1)))),""))
G2: =IF(F2="","",IF(COUNTIF(INDIRECT("c"&F2+1&":c"&$D$1),"0"),MIN(IF((INDIRECT("c"&F2&":c"&$D$1)=0)*(INDIRECT("c"&F2+1&":c"&$D$1+1)>0),ROW(INDIRECT("A"&F2&":A"&$D$1)))),""))
G3: =IF(F3="","",IF(COUNTIF(INDIRECT("c"&F3+1&":c"&$D$1),"0")=ROWS(INDIRECT("c"&F3+1&":c"&$D$1)),$D$1,IF(COUNTIF(INDIRECT("c"&F3+1&":c"&$D$1),"0"),MIN(IF((INDIRECT("c"&F3&":c"&$D$1)=0)*(INDIRECT("c"&F3+1&":c"&$D$1+1)>0),ROW(INDIRECT("A"&F3&":A"&$D$1)))),"")))
*NOTE an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual < Enter >.
These result in the start and end rows of any dry periods, and the formulae in F3:G3 can be copied down as far as is necessary to cover all the periods - I've copied them to row 500 in the attached file. If you add more data to the bottom of columns A:C, the formulae should automatically adjust to it, though you might have the copy the formula in F and G (and other columns) further.
The following formulae work out the start and end rows of any periods of rain:
I2: =IF(F2="","",IF(F2>2,2,G2+1))
J2: =IF(F3="","",F3-1)
I3: =IF(OR(J2="",F3="",G3=""),"",IF(G3=$D$1,"",G3+1))
J3: =IF(F4="","",F4-1)
and again, I3 and J3 can be copied down as far as needed (to row 500 in the attached file). It is relatively easy then to get the number of continuous days of rain for each period, with this formula in K2:
=IF(I2="","",J2-I2+1)
which is then copied down. The month in which each wet spell starts can be found with this formula in L2:
=IF(I2="","",INDEX($B:$B,I2))
and a similar formula in M2 can find the month where the period finishes:
=IF(J2="","",INDEX($B:$B,J2))
(though I don't actually make use of this). Again, these are copied down to row 500. I've colour-coded the columns that apply to dry spells and wet spells.
You could put this formula in N2:
=L2=M2
which will return TRUE if the start and end months are the same and FALSE if they are different.
I've put your table from column O onwards, with these formulae in the cells stated:
P2: =COUNTIFS($C:$C,">0",$B:$B,$O2)
Q2: =SUMPRODUCT(--(INDIRECT("L2:L"&COUNT($F:$F))=$O2))
R2: =SUMPRODUCT((INDIRECT("L2:L"&COUNT($F:$F))=$O2)*(INDIRECT("K2:K"&COUNT($F:$F))=--RIGHT(R$1,2)))
The formula in R2 can then be copied across as far as is required - note that I have changed the wording in the headings of the table, so that I can get the number of days easily from the end. The formulae in the table can then the copied down to row 13.
This does more than you actually asked for, but maybe you can make use of the extra features.
Hope this helps.
Pete
Last edited by Pete_UK; 04-04-2016 at 06:06 AM.
Thank you Mr.FlameRetired & Mr.Pete both of your formulas worked nice, formulas given by Mr.FlameRetired are simple and effective, and Mr. Pete has given more information and actually they will be helpful for me thanks again both of you.
i have one more request what if i don't want to split spell length (wet or dry) into different months as asked by Mr.Pete, in June 1972 where there are 7 continuous wet days that finish in July 1972 (6 in June, 1 in July) i want to count that 7 day spell in june month only not in july, actually i want result in this format, i didn't mention this in question sorry for that.
thank you
My formulae just record the wet spells against the starting month, so that 7-day period starting in June 1972 will be counted as 7 days in June.
Hope this helps.
Pete
ya i got it now, thank you Mr.Pete and Mr.FlameRetired again for the help.
thanks
regards
Rohith A N
Glad to be able to help - thanks for the rep.
If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.
Pete
What Pete said, and thanks for rep.
I need to calculate number of times the rainfall value is consecutively different than zero (dry spell). What the expressions would be?
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks