Looping formula in same columm / dynamic range?
Hi all,
I had to make an account to find answers, so is my first post!
What I need is the difference (max-min) in A column for each interval between zeros. Its possible with formulas only?
This table is an example, real one had 3000 lines and too many sheets
excel.JPG
oh! IMPORTANT:
the amount of cells between 0 is NOT always 3, it always varies .... the image tricks
excel.JPG
Hello gdieterich and Welcome to Excel Forum.
For future reference please upload the sample file instead of a screen shot.
I believe that the following array entered formula* will work in the 2007 version:Formula:
=IF(B3>0,"",A3-INDEX(A$2:A2,LARGE(IFERROR(ROW($1:1)/(B$2:B2=0),FALSE),1)))
*1) Select cell C3,
2) Paste the formula into the formula bar,
3) Simultaneously press the Ctrl, Shift and Enter keys,
4) Double click the fill handle to copy the formula down column C.
To upload a spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
JeteMc,
Thank you very much for your response, it's 99% what I want, helped me a lot.
I attached the whole sheet, Its more explained there.
now I just need to filter the highest values (final total time) and shrink the results one below the other.
FYI, this is data of induction furnaces that melting cast iron, so with this formulas I can automate the extract of melting time.
This starts out being pretty promising, but then fails due to some of the stating times not having corresponding ending times and visa versa. For example I don't understand why M461 doesn't read Start, nor why M627 doesn't read Finish.
At any rate if those issues are sorted then perhaps the following will help with getting total times.
1) Column U shows a list of Start times using*: =IFERROR(INDEX(N$2:N$2421,SMALL(IF(M$2:M$2421="Start",ROW($2:$2421)-1),ROW(1:1))),"")
2) Column V shows a list of Finish times using*: =IFERROR(INDEX(N$2:N$2421,SMALL(IF(M$2:M$2421="Start",ROW($2:$2421)-1),ROW(1:1))),"")
3) Column W shows a list of Total times using: =IFERROR(V5-U5,"")
*Denotes array entered formulas which need to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER before they are copied down the column.
Note that the formatting for the Totals column is: [h]:mm:ss
Let us know if you have any questions.
Thank you very much JeteMc, works like a charm!!!
Please find attached solution
Very easy to do if you make cluster group
ADD reputation if you like my solution
Thank you very much JeteMc, works like a charm!!!
ashishkhurana93 thank you for your time, but the other template fits better hehe.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
You're Welcome and thank you for the feedback. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks