# Growth between 3 cells in Google Sheets

I am creating a data wall to compare student achievement across 3 time periods Beginning of the Year(H2), Middle of the Year (I2) and End of the Year(J2) and having the difference recorded in K2. I have tried using =MAX(abs(H\$2-I\$2), abs(I\$2-J\$2), abs(H\$2-J\$2)) The issue I am having is when I have only entered Beginning of the Year(H2) and Middle of the Year (I2). I am not getting the difference in K2 but the difference between Middle of the Year (I2) and End of the Year(J2).

I would like a formula if possible to find the diffrence Beginning of the Year(H2) and Middle of the Year (I2) for mid year reports and then when End of the Year(J2) data is entered get the difference between Middle of the Year (I2) and End of the Year(J2) for end of year reports. Is this possible? I hope I have made myself clear.

Thank you

An example of what I am talking about

• I included two array formulas in an answer. Version 1 is based on at least one of either pair of values being pooulated. Version 2 is based on requiring the first pair of values, at a minimum, to be populated. Commented May 26 at 15:54
• Your intial formula works fine but if the student get a lower score in the mid year (I2) or end of the year (J2), I need to display a negative score not just the difference between I2 and J2. Can the formula be modified to produce a negative number? Commented May 28 at 8:23
• Your requirements are changing. Your sample formulas use ABS which returns the absolute difference and flags the greatest disparity between 2 tests. Removing ABS and swapping the order of the subtraction while keeping MAX will return only the largest increase (or smallest decrease). Changing to MIN will return the smallest increase (or largest decrease). None of the 3 approaches indicates whether the final score is the highest of the 3. In order to give you the right equation one needs to understand what you are seeking to understand from the result and/or how it will be used. Commented May 28 at 10:49
• You should explain it in plain language first. For example, "I want to flag the greatest disparity between 1&2, 2&3, 1&3." or "If 3 is not the highest score then return the difference between 3 and the highest score" or "if 3 not highest, return difference between 3 and average of other scores" or "if 3 is the lowest score.." or "if all scores decline..." or "if 2nd score increases but 3rd decreases.." etc. etc. Commented May 28 at 10:58

# Array Formula Version 1

• put the formula in row 1 and clear any other values from the column.
• Returns the results for all populated rows in columns H through J.
• A row is considered populated if both of Column H and I, or both of Column I and J, contain a value (otherwise the row is skipped).
``````={"type_column_heading_here";
MAP(H2:H, I2:I, J2:J, LAMBDA(h, i, j,
LET(
f, LEN(h)*LEN(i)>0,
s, LEN(i)*LEN(j)>0,
IF(f+s, MAX(f*ABS(h-i),
s*ABS(i-j)),))))}
``````

### Explanation

1. MAP passes the ranges H2:H, I2:I, and J2:J, row by row, into a LAMBDA function that stores the values from the current row in `h`, `i`, and `j`, respectively.
2. LET is used to store values in named variables. It is not necessary, but avoids the need to repeat calculations.
3. `f` stores the result of testing if the product of the length of each of the `f`irst pair of values, `h` and `i`, is greater than zero.
1. `f=TRUE` if both values in the first pair have a length greater than zero.
2. `f=FALSE` if one or more of the values is empty.
4. Similarly, `s` stores the result of testing if the product of the length of each of the `s`econd pair of values, `i` and `j`, is greater than zero.
5. If neither `f` nor `s` is TRUE, no calculation is possible and the LAMBDA's formula returns an empty value. This is determined by testing if `f`+`s` is a non-zero number. Sheets will interpret TRUE and FALSE as 1 and 0 in a calculation, and interpret any non-zero number as TRUE and 0 as FALSE in IF conditions:
``````FALSE+FALSE=0+0=0
TRUE+FALSE=1+0=1
TRUE+TRUE=1+1=2

IF(0,true,false)=FALSE
IF(1,true,false)=TRUE
IF(2,true,false)=TRUE
``````
6. If at least one of `f` or `s` is TRUE, the LAMBDA's formula will calculate the MAX of the following:
1. The product of `f` and the absolute value of `h`-`i`
2. The product of `s` and the absolute value of `i`-`j`
7. The reason the product of `f` and `s` and the respective ABS functions is calculated in the previous step is that while at least one of `f` or `s` is TRUE, this ensures that if both are not TRUE, the ABS function associated with the one that is FALSE is replaced by an empty value. This approach is an alternative to IF functions:
``````TRUE*ABS(h-i)= 1*ABS(h-i)= ABS(h-i)

FALSE*ABS(h-i)= 0*ABS(h-i)= 0
``````

# Array Formula Version 2

• This version also returns the results for all populated rows in columns H through J.
• In this version, however, a row is considered populated if both Column H and I contain a value (Column J is not factored in to deciding what rows should be skipped).
``````={"type_column_heading_here";
MAP(H2:H, I2:I, J2:J, LAMBDA(h, i, j,
IF(LEN(h)*LEN(i),
MAX(ABS(h-i),
IF(LEN(j),ABS(i-j),)),)))}
``````

### Explanation

1. Again uses MAP and LAMBDA to process each row.
2. IF returns the MAX function's result if neither `LEN(h)` nor `LEN(i)` return 0
3. `ABS(i-j)` is included in the MAX function only if `j` is populated.