Increment by 18

[John]

I import data form Word to Excel on a weekly basis to a sheet called John T.
I have also created another sheet called John in the same Work Book.
I have created a formula in John to check if 5 values in John T are greater
than zero(0), if they are greater than zero datafill John with GREATER THAN 0
if not leave it blank.
=IF('John T'!B10>0,"GREATER THAN 0",IF('John T'!B11>0,"GREATER THAN
0",IF('John T'!B12>0,"GREATER THAN 0",IF('John T'!B13>0,"GREATER THAN
0",IF('John T'!B14>0,"GREATER THAN 0","")))))
It works ok but I need to know if there is a quick way to increment this
formula by creating an Auto Fill or Series in John so I can get it to jump
ahead 18 spots on John T and have it increment all the 'John Ts' by 18 and
continue on for each 18 increment.
In other words =IF('John T'!B28>0,"GREATER THAN 0",IF('John
T'!B29>0,"GREATER THAN 0",IF('John T'!B30>0,"GREATER THAN 0",IF('John
T'!B31>0,"GREATER THAN 0",IF('John T'!B32>0,"GREATER THAN 0","")))))

Thanks

 

[Jacob Skaria]

Hi John

Your formula can be shortened using OR() as below
=IF(OR('John T'!B10>0,'John T'!B11>0,'John T'!B12>0,'John T'!B13>0,'John
T'!B14>0),"GREATER THAN 0","")

it can be shortened further if you use COUNTIF() as below
=IF(COUNTIF('John T'!B10:B14,">0"),"Greater than 0","")


'Now the trick to increment 18. Try the below formula and copy down as
required
=IF(COUNTIF(INDIRECT("'jOHN
t'!B"&18*(ROW(1:1)-1)+10&":B"&18*(ROW(1:1)-1)+14),">0"),"Greater than 0","")

Try and feedback

If this post helps click Yes