Climate: Calculate Consecutive Days of Rain2019 Community Moderator ElectionGetting the 30 mins max sum of Column B per dayAdd business days in start date then subtract 1 business day (Excel)Calculating change between duplicate rowsExcel count minimum required to reach total of consecutive cellsExcel: Return header for searched value (multiple instances)Increment a date by a number of days, months or yearsRetutn only Buisness days in custom date function in excelCount consecutive instances in columnFind time difference between two columns based on unique id and add the absence daysThe DAX code calculating the sum of maximum values per day
Virginia employer terminated employee and wants signing bonus returned
Reverse string, can I make it faster?
In the late 1940’s to early 1950’s what technology was available that could melt a LOT of ice?
Should I tell my boss the work he did was worthless
NASA's RS-25 Engines shut down time
'The literal of type int is out of range' con número enteros pequeños (2 dígitos)
What are actual Tesla M60 models used by AWS?
Why is computing ridge regression with a Cholesky decomposition much quicker than using SVD?
Can one live in the U.S. and not use a credit card?
How do I express some one as a black person?
Are all players supposed to be able to see each others' character sheets?
Do I really need to have a scientific explanation for my premise?
If I receive an SOS signal, what is the proper response?
How is the wildcard * interpreted as a command?
Why would one plane in this picture not have gear down yet?
Are there historical instances of the capital of a colonising country being temporarily or permanently shifted to one of its colonies?
Word for a person who has no opinion about whether god exists
Plausibility of Mushroom Buildings
Difference on montgomery curve equation between EFD and RFC7748
meaning and function of 幸 in "则幸分我一杯羹"
Shifting between bemols (flats) and diesis (sharps)in the key signature
Find longest word in a string: are any of these algorithms good?
How to secure an aircraft at a transient parking space?
Does a warlock using the Darkness/Devil's Sight combo still have advantage on ranged attacks against a target outside the Darkness?
Climate: Calculate Consecutive Days of Rain
2019 Community Moderator ElectionGetting the 30 mins max sum of Column B per dayAdd business days in start date then subtract 1 business day (Excel)Calculating change between duplicate rowsExcel count minimum required to reach total of consecutive cellsExcel: Return header for searched value (multiple instances)Increment a date by a number of days, months or yearsRetutn only Buisness days in custom date function in excelCount consecutive instances in columnFind time difference between two columns based on unique id and add the absence daysThe DAX code calculating the sum of maximum values per day
I have a data column rainfall, and a column is on that date;
I intend to extract Consecutive Days of Rain from it in a separate column.
With the formula below, I extracted Consecutive :
columnF=IF(B2>0,SUM(F1)+1,0)
But the result is in a column and I can not separate them like in attached picture (see desired result).
If we want to extract the continuity of rainfall in a separate column
How to do?
My result: 
Desired result:
excel vba
edited Mar 7 at 7:37
Pᴇʜ
23.8k62952
asked Mar 7 at 6:18
ali reza karbalaeeali reza karbalaee
61
New contributor
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have a data column rainfall, and a column is on that date;
I intend to extract Consecutive Days of Rain from it in a separate column.
With the formula below, I extracted Consecutive :
columnF=IF(B2>0,SUM(F1)+1,0)
But the result is in a column and I can not separate them like in attached picture (see desired result).
If we want to extract the continuity of rainfall in a separate column
How to do?
My result:
Desired result:
excel vba
edited Mar 7 at 7:37
Pᴇʜ
23.8k62952
asked Mar 7 at 6:18
ali reza karbalaeeali reza karbalaee
61
New contributor
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Calculate two helper columns (hidden is ok). The 1st you have. The 2nd will have the max of the running total. So 0,2,2,0,2,2,0,3,3,3,0,4,4,4,4,0. Somehow leverage the 1st helper column to derive the 2nd helper column. All the cells in the dx#day columns have the formula=if(column() - column(dx1day) + 1 = 2nd-helper-column, 1st-helper-column, "")Where the helper column address references are given with an absolute Column Letter and relative row number (which should be the same as the row of the formula). This way when the formula is copied, the row reference updates properly.
– Ted D.
Mar 7 at 7:12
add a comment |
I have a data column rainfall, and a column is on that date;
I intend to extract Consecutive Days of Rain from it in a separate column.
With the formula below, I extracted Consecutive :
columnF=IF(B2>0,SUM(F1)+1,0)
But the result is in a column and I can not separate them like in attached picture (see desired result).
If we want to extract the continuity of rainfall in a separate column
How to do?
My result:
Desired result:
excel vba
edited Mar 7 at 7:37
Pᴇʜ
23.8k62952
asked Mar 7 at 6:18
ali reza karbalaeeali reza karbalaee
61
New contributor
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have a data column rainfall, and a column is on that date;
I intend to extract Consecutive Days of Rain from it in a separate column.
With the formula below, I extracted Consecutive :
columnF=IF(B2>0,SUM(F1)+1,0)
But the result is in a column and I can not separate them like in attached picture (see desired result).
If we want to extract the continuity of rainfall in a separate column
How to do?
My result:
Desired result:
excel vba
excel vba
edited Mar 7 at 7:37
Pᴇʜ
23.8k62952
asked Mar 7 at 6:18
ali reza karbalaeeali reza karbalaee
61
New contributor
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 7 at 7:37
Pᴇʜ
23.8k62952
asked Mar 7 at 6:18
ali reza karbalaeeali reza karbalaee
61
New contributor
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 7 at 7:37
Pᴇʜ
23.8k62952
edited Mar 7 at 7:37
Pᴇʜ
23.8k62952
edited Mar 7 at 7:37
Pᴇʜ
23.8k62952
23.8k62952
asked Mar 7 at 6:18
ali reza karbalaeeali reza karbalaee
61
New contributor
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Mar 7 at 6:18
ali reza karbalaeeali reza karbalaee
61
asked Mar 7 at 6:18
ali reza karbalaeeali reza karbalaee
61
61
New contributor
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
ali reza karbalaee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Calculate two helper columns (hidden is ok). The 1st you have. The 2nd will have the max of the running total. So 0,2,2,0,2,2,0,3,3,3,0,4,4,4,4,0. Somehow leverage the 1st helper column to derive the 2nd helper column. All the cells in the dx#day columns have the formula=if(column() - column(dx1day) + 1 = 2nd-helper-column, 1st-helper-column, "")Where the helper column address references are given with an absolute Column Letter and relative row number (which should be the same as the row of the formula). This way when the formula is copied, the row reference updates properly.
– Ted D.
Mar 7 at 7:12
add a comment |
Calculate two helper columns (hidden is ok). The 1st you have. The 2nd will have the max of the running total. So 0,2,2,0,2,2,0,3,3,3,0,4,4,4,4,0. Somehow leverage the 1st helper column to derive the 2nd helper column. All the cells in the dx#day columns have the formula=if(column() - column(dx1day) + 1 = 2nd-helper-column, 1st-helper-column, "")Where the helper column address references are given with an absolute Column Letter and relative row number (which should be the same as the row of the formula). This way when the formula is copied, the row reference updates properly.
– Ted D.
Mar 7 at 7:12
Calculate two helper columns (hidden is ok). The 1st you have. The 2nd will have the max of the running total. So 0,2,2,0,2,2,0,3,3,3,0,4,4,4,4,0. Somehow leverage the 1st helper column to derive the 2nd helper column. All the cells in the dx#day columns have the formula
=if(column() - column(dx1day) + 1 = 2nd-helper-column, 1st-helper-column, "") Where the helper column address references are given with an absolute Column Letter and relative row number (which should be the same as the row of the formula). This way when the formula is copied, the row reference updates properly.– Ted D.
Mar 7 at 7:12
Calculate two helper columns (hidden is ok). The 1st you have. The 2nd will have the max of the running total. So 0,2,2,0,2,2,0,3,3,3,0,4,4,4,4,0. Somehow leverage the 1st helper column to derive the 2nd helper column. All the cells in the dx#day columns have the formula
=if(column() - column(dx1day) + 1 = 2nd-helper-column, 1st-helper-column, "") Where the helper column address references are given with an absolute Column Letter and relative row number (which should be the same as the row of the formula). This way when the formula is copied, the row reference updates properly.– Ted D.
Mar 7 at 7:12
add a comment |
1 Answer
1
active
oldest
votes
Following from Ted D's comment... a possible formula for the second helper column would be (in cell G2):
=IF(F2=0,0,MATCH(0,F3:F999,0)-1+F2)
This looks for the first 0 in the counter column (F) beneath our row (looking around 1000 rows down - that 999 can be changed as appropriate). We use that plus our current counter to give us the size of the block.
From there, we can make the columns with the dx1day etc. titles and populate them with the formula:
=IF($G2=VALUE(SUBSTITUTE(MID(H$1,3,999),"day","")),$F2,"")
This is parsing the number from dx1day at the top of the column (in this case H) (if you did it with just numbers and a custom format you could save that bit*). We then check if the current "block size" value (in column G) is the same as that in the header. If it is we copy the counter value (from column F), otherwise blank.
You can get away from this second helper column by replacing the $G2 with (IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2)) - but if makes it a bit more of a headache to understand (and debug :-)
*the way for this is to do custom format with the "Type" being "dx"0"day"... then the formula becomes =IF($G2=H$1,$F2,"") or =IF((IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2))=H$1,$F2,"") for the no-second-helper-column version
answered Mar 7 at 8:56
CaptainCaptain
1,683813
Thank you very much
– ali reza karbalaee
Mar 7 at 11:03
@alirezakarbalaee - if this works for you, please accept the answer
– Captain
Mar 8 at 8:33
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
ali reza karbalaee is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55037217%2fclimate-calculate-consecutive-days-of-rain%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Following from Ted D's comment... a possible formula for the second helper column would be (in cell G2):
=IF(F2=0,0,MATCH(0,F3:F999,0)-1+F2)
This looks for the first 0 in the counter column (F) beneath our row (looking around 1000 rows down - that 999 can be changed as appropriate). We use that plus our current counter to give us the size of the block.
From there, we can make the columns with the dx1day etc. titles and populate them with the formula:
=IF($G2=VALUE(SUBSTITUTE(MID(H$1,3,999),"day","")),$F2,"")
This is parsing the number from dx1day at the top of the column (in this case H) (if you did it with just numbers and a custom format you could save that bit*). We then check if the current "block size" value (in column G) is the same as that in the header. If it is we copy the counter value (from column F), otherwise blank.
You can get away from this second helper column by replacing the $G2 with (IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2)) - but if makes it a bit more of a headache to understand (and debug :-)
*the way for this is to do custom format with the "Type" being "dx"0"day"... then the formula becomes =IF($G2=H$1,$F2,"") or =IF((IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2))=H$1,$F2,"") for the no-second-helper-column version
answered Mar 7 at 8:56
CaptainCaptain
1,683813
Thank you very much
– ali reza karbalaee
Mar 7 at 11:03
@alirezakarbalaee - if this works for you, please accept the answer
– Captain
Mar 8 at 8:33
add a comment |
Following from Ted D's comment... a possible formula for the second helper column would be (in cell G2):
=IF(F2=0,0,MATCH(0,F3:F999,0)-1+F2)
This looks for the first 0 in the counter column (F) beneath our row (looking around 1000 rows down - that 999 can be changed as appropriate). We use that plus our current counter to give us the size of the block.
From there, we can make the columns with the dx1day etc. titles and populate them with the formula:
=IF($G2=VALUE(SUBSTITUTE(MID(H$1,3,999),"day","")),$F2,"")
This is parsing the number from dx1day at the top of the column (in this case H) (if you did it with just numbers and a custom format you could save that bit*). We then check if the current "block size" value (in column G) is the same as that in the header. If it is we copy the counter value (from column F), otherwise blank.
You can get away from this second helper column by replacing the $G2 with (IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2)) - but if makes it a bit more of a headache to understand (and debug :-)
*the way for this is to do custom format with the "Type" being "dx"0"day"... then the formula becomes =IF($G2=H$1,$F2,"") or =IF((IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2))=H$1,$F2,"") for the no-second-helper-column version
answered Mar 7 at 8:56
CaptainCaptain
1,683813
Thank you very much
– ali reza karbalaee
Mar 7 at 11:03
@alirezakarbalaee - if this works for you, please accept the answer
– Captain
Mar 8 at 8:33
add a comment |
Following from Ted D's comment... a possible formula for the second helper column would be (in cell G2):
=IF(F2=0,0,MATCH(0,F3:F999,0)-1+F2)
This looks for the first 0 in the counter column (F) beneath our row (looking around 1000 rows down - that 999 can be changed as appropriate). We use that plus our current counter to give us the size of the block.
From there, we can make the columns with the dx1day etc. titles and populate them with the formula:
=IF($G2=VALUE(SUBSTITUTE(MID(H$1,3,999),"day","")),$F2,"")
This is parsing the number from dx1day at the top of the column (in this case H) (if you did it with just numbers and a custom format you could save that bit*). We then check if the current "block size" value (in column G) is the same as that in the header. If it is we copy the counter value (from column F), otherwise blank.
You can get away from this second helper column by replacing the $G2 with (IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2)) - but if makes it a bit more of a headache to understand (and debug :-)
*the way for this is to do custom format with the "Type" being "dx"0"day"... then the formula becomes =IF($G2=H$1,$F2,"") or =IF((IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2))=H$1,$F2,"") for the no-second-helper-column version
answered Mar 7 at 8:56
CaptainCaptain
1,683813
Following from Ted D's comment... a possible formula for the second helper column would be (in cell G2):
=IF(F2=0,0,MATCH(0,F3:F999,0)-1+F2)
This looks for the first 0 in the counter column (F) beneath our row (looking around 1000 rows down - that 999 can be changed as appropriate). We use that plus our current counter to give us the size of the block.
From there, we can make the columns with the dx1day etc. titles and populate them with the formula:
=IF($G2=VALUE(SUBSTITUTE(MID(H$1,3,999),"day","")),$F2,"")
This is parsing the number from dx1day at the top of the column (in this case H) (if you did it with just numbers and a custom format you could save that bit*). We then check if the current "block size" value (in column G) is the same as that in the header. If it is we copy the counter value (from column F), otherwise blank.
You can get away from this second helper column by replacing the $G2 with (IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2)) - but if makes it a bit more of a headache to understand (and debug :-)
*the way for this is to do custom format with the "Type" being "dx"0"day"... then the formula becomes =IF($G2=H$1,$F2,"") or =IF((IF($F2=0,0,MATCH(0,$F3:$F999,0)-1+$F2))=H$1,$F2,"") for the no-second-helper-column version
answered Mar 7 at 8:56
CaptainCaptain
1,683813
answered Mar 7 at 8:56
CaptainCaptain
1,683813
answered Mar 7 at 8:56
CaptainCaptain
1,683813
answered Mar 7 at 8:56
CaptainCaptain
1,683813
1,683813
Thank you very much
– ali reza karbalaee
Mar 7 at 11:03
@alirezakarbalaee - if this works for you, please accept the answer
– Captain
Mar 8 at 8:33
add a comment |
Thank you very much
– ali reza karbalaee
Mar 7 at 11:03
@alirezakarbalaee - if this works for you, please accept the answer
– Captain
Mar 8 at 8:33
Thank you very much
– ali reza karbalaee
Mar 7 at 11:03
Thank you very much
– ali reza karbalaee
Mar 7 at 11:03
@alirezakarbalaee - if this works for you, please accept the answer
– Captain
Mar 8 at 8:33
@alirezakarbalaee - if this works for you, please accept the answer
– Captain
Mar 8 at 8:33
add a comment |
ali reza karbalaee is a new contributor. Be nice, and check out our Code of Conduct.
ali reza karbalaee is a new contributor. Be nice, and check out our Code of Conduct.
ali reza karbalaee is a new contributor. Be nice, and check out our Code of Conduct.
ali reza karbalaee is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55037217%2fclimate-calculate-consecutive-days-of-rain%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Calculate two helper columns (hidden is ok). The 1st you have. The 2nd will have the max of the running total. So 0,2,2,0,2,2,0,3,3,3,0,4,4,4,4,0. Somehow leverage the 1st helper column to derive the 2nd helper column. All the cells in the dx#day columns have the formula
=if(column() - column(dx1day) + 1 = 2nd-helper-column, 1st-helper-column, "")Where the helper column address references are given with an absolute Column Letter and relative row number (which should be the same as the row of the formula). This way when the formula is copied, the row reference updates properly.– Ted D.
Mar 7 at 7:12