ExcelBanter - Increment by 18

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Increment by 18 (https://www.excelbanter.com/excel-worksheet-functions/244894-increment-18-a.html)

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'!B100,"GREATER THAN 0",IF('John T'!B110,"GREATER THAN
0",IF('John T'!B120,"GREATER THAN 0",IF('John T'!B130,"GREATER THAN
0",IF('John T'!B140,"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'!B280,"GREATER THAN 0",IF('John
T'!B290,"GREATER THAN 0",IF('John T'!B300,"GREATER THAN 0",IF('John
T'!B310,"GREATER THAN 0",IF('John T'!B320,"GREATER THAN 0","")))))

Thanks

Hi John

Your formula can be shortened using OR() as below
=IF(OR('John T'!B100,'John T'!B110,'John T'!B120,'John T'!B130,'John
T'!B140),"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
---------------
Jacob Skaria

"John" wrote:

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'!B100,"GREATER THAN 0",IF('John T'!B110,"GREATER THAN
0",IF('John T'!B120,"GREATER THAN 0",IF('John T'!B130,"GREATER THAN
0",IF('John T'!B140,"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'!B280,"GREATER THAN 0",IF('John
T'!B290,"GREATER THAN 0",IF('John T'!B300,"GREATER THAN 0",IF('John
T'!B310,"GREATER THAN 0",IF('John T'!B320,"GREATER THAN 0","")))))

Thanks