Need to adjust formula accordingly
 

Hi, I had the formula:

=SUM(N(OFFSET($A$9,0,SMALL(IF((L9:BH90)*(MOD(COLU MN(L9:BH9)-12,8)=0),COLUMN(L9:BH9),COLUMN(IV9)),{1,2,3,4,5})-1,1,1)))

I had to ad in extra columns for my nightshift

I tried to adjust the formula as follows:

=SUM(N(OFFSET($A$14,0,SMALL(IF((K14:BM140)*(MOD(C OLUMN(K14:BM14)-12,8)=0),COLUMN(K14:BM14),COLUMN(IV14)),{1,2,3,4,5 })-1,1,1)))

but the outcome is not the same,

what do I have to do to do any adjustments in future, should I wish t
ad more columns in between.

My cells a
K14, T14, AC14, AL14, AU14, BD14, BM14 (Every 9th cell)

help will be most appreciated

--
Message posted from http://www.ExcelForum.com

Need to adjust formula accordingly
 

Change the eight to a nine.

sonar < wrote:
Hi, I had the formula:

=SUM(N(OFFSET($A$9,0,SMALL(IF((L9:BH90)*(MOD(COLU MN(L9:BH9)-12,8)=0),COLUMN(L9:BH9),COLUMN(IV9)),{1,2,3,4,5})-1,1,1)))

I had to ad in extra columns for my nightshift

I tried to adjust the formula as follows:

=SUM(N(OFFSET($A$14,0,SMALL(IF((K14:BM140)*(MOD(C OLUMN(K14:BM14)-12,8)=0),COLUMN(K14:BM14),COLUMN(IV14)),{1,2,3,4,5 })-1,1,1)))

but the outcome is not the same,

what do I have to do to do any adjustments in future, should I wish to
ad more columns in between.

My cells a
K14, T14, AC14, AL14, AU14, BD14, BM14 (Every 9th cell)

help will be most appreciated.


---
Message posted from http://www.ExcelForum.com/

Need to adjust formula accordingly
 

The formula, if I am not wrong, seems to see my BE14 (overtime) cell
and not my BD (normal time) cell, any reason?

What does the -12 and the 9 mean

--
Message posted from http://www.ExcelForum.com

Need to adjust formula accordingly
 

Well, I'm not really sure what you are trying to do with this formula,
but it seems that the 12 should be changed to 11 since you added a
column to the left (K=column 11) instead of starting at L=column12. The
9 looks at every ninth column.

sonar < wrote:

The formula, if I am not wrong, seems to see my BE14 (overtime) cell,
and not my BD (normal time) cell, any reason?

What does the -12 and the 9 mean?


---
Message posted from http://www.ExcelForum.com/