Log In
Register
Home
Active Member
June 21, 2023
Offline
Hi, I am using Excel 2016 on a PC
I have a column (K) starting at row 10, that I want to run a calculation (K$9/2) so that when column (H) has the value equal to (K$7, which is a sequential number 1, 2, 3 etc) the formula will do nothing for 9 rows after the sequential number in (H), then do the calculations for 81 rows, then repeat 9 blanks & then 81 calc’s & so on.
The big problem for me, is the row in column (H) that will have the 1st number in, is variable, it could be in any row from 10 to 71.
Column (L) will have the same formula as (K), & so will the following columns each looking for their own sequential number in (H)
It would be brilliant to get this as a formula in the cells, and in each column, but if more rows/columns are needed then so be it.
I can start the process easily enough, but getting it to stop & then start again after 9 blank cells & repeating that eludes me.
Any help would be much appreciated as I am slowly going bald trying to work it out!
Regards Roger
P.S. The attached file show’s how I would like the formula to work…hopefully
See Answer
Trusted Members
October 17, 2018
Offline
Maybe I understand your question but I am guessing that you'll have to do this using VBA.
Stop and restarting a new sequence will require extensive formula building and maybe (many) helper columns.
Trusted Members
Moderators
November 1, 2018
Offline
I think this works:
K19: =IF(ROW()<MATCH(K$7,$H:$H,0)+9,"",IF(MOD(ROW()-MATCH(K$7,$H:$H,0),91)<9,"",K$9/2))
and copy across and down.
Answers Post
Trusted Members
October 17, 2018
Offline
@Velouria: I'm getting a circular reference when the formula is copied acros and only in L19
Trusted Members
Moderators
November 1, 2018
Offline
Oh yes. I hadn't spotted that the values in columns D:I depend on the values in columns J:S of the row above. I think this should fix the problem:
=IFERROR(IF(ROW()<MATCH(K$7,$H$1:$H19,0)+9,"",IF(MOD(ROW()-MATCH(K$7,$H$1:$H19,0),91)<9,"",K$9/2)),"")
Trusted Members
October 17, 2018
Offline
That did the trick !!! 
Active Member
June 21, 2023
Offline
Thank you guys, This has worked brilliantly!. I really appreciate you taking your time to help resolve this conundrum for me! I had come up with
=IF(AND(ROW()-L$6>8,ROW()-L$6<90),L$9/2,IF(AND(ROW()-L$6>98,ROW()-L$6<180),L$9/2,IF(AND(ROW()-L$6>188,ROW()-L$6<270),L$9/2,IF(AND(ROW()-L$6>278,ROW()-L$6<360),L$9/2,""))))
but your's is much neater, I was also getting the circular ref's & was in the process of trying to resolve that, again without much success!
I thank you both once again ;0)
1 Guest(s)
