0

ABS function does not replace last row in table

Ed Crawford

March 01, 2019 22:49

None

Follow

I am using the ABS function to convert blanks to zeros but it fails to replace the last row in the table with a zero.  Any ideas?

 

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Janice Janczyn March 03, 2019 17:29

  Hi Ed, 

  When you evaluate your data, does it return 7 or 8 items? It may return 7 because it detects the last non-blank item as the end of the column. That said, I recommend using REPLACE instead of ABS to replace blanks (or spaces or null characters) with 0:

       REPLACE( A:A, BLANK(), 0 )  

  or

       REPLACE( A:A, ARRAY( BLANK(), " ", "" ), 0 )

  Thank you,
               Janice