Ignoring blank cells in array formula that identifies biggest difference between cells (Google Sheets / Excel)

1
votes

I'm making an excel document with data from 2006 to 2016, where I'm trying to find the biggest changes in value from one year to the next. But for some years the data is missing.

I have a row of data like this:

21.5 -- 20.8 20.2 -- 21.0 -- 20.8 21.2 21.1

So a row of numbers with some blank cells here and there. Then I have a formula to identify the biggest value difference between neighbouring cells:

=ArrayFormula(MAX(ABS(B12:K12 - C12:L12)))

The answer should be 0.8, but because Google Sheets identifies the blank cells as 0, I get 21.5. Is there any way for Google Sheets / Excel to ignore the blank cells in the array? I've seen some examples on how to ignore blank cells, but I can't get them to work with my other formula.

Grateful for any help!

1
excelgoogle-sheets

1 Answers

0
votes

Try formula:

=ArrayFormula(MAX(ABS(if(B12:K12=0,C12:L12,B12:K12) - if(C12:L12=0,B12:K12,C12:L12))))

The right answer is 0.6:

20.8 – 20.2 = 0.6