Finding the maximum difference between two same sized rows while ignoring blank cells in Excel

Question

I have two rows, A (assumed to not have any nulls) and B (can contain blank cells) in Excel. A and B are both the same length.

What is the correct Excel formula to compute the maximum positive increase between A and B?

Example:

Row A = [10, 4, 8, 20, 4, 5, 7, 2, 9, 5]
Row B = [0, 20, 4, null, 10, 9, 2, null, null, null]

Expected answer for A - B: max([10 - 0, 4 - 20, 8 - 4, 4 - 10, 5 - 9, 7 - 2]) = max(10, -16, 4, -6, -4, 5) = 10

If Row B is all null, the expected answer for A - B is null.

I would like to avoid creating a new row to compute this value.

Answer 1

Suppose you have row A data from A1 to J1 and row B data from A2 to J2.

Then you can use this array function to skip the difference if cell is blank in the row B then calculate the maximum of differences using the array function as:

=MAX(IF(NOT(ISBLANK(A2:J2)),A1:J1-A2:J2))

To enter the array function, after you type or paste above function, press Ctrl + Shift + Enter.

References: https://www.excel-easy.com/functions/array-formulas.html and https://exceljet.net/formula/max-if-criteria-match

Answer 2

Or try this non-array formula,

=AGGREGATE(14,6,(A2:J2<>"")*(A1:J1-A2:J2),1)