This is the first time I have asked a quetion. I searched the community about finding the last occupied cell in a column and found this (=SUMPRODUCT(MAX(ROW($C$52:$C$190)*($C$52:$C$190<>"")))). It returned the row number and worked for me.
I tried to look up the functions and how it works but there was nothing about multiplying rows and “<>” and how “max” would work in this equation. Could someone explain this equation to me in detail? I like to know how things work.
Thanks
What you have written indeed doesn’t work: Needs to read:
{=SUMPRODUCT(MAX(ROW($C$52:$C$190)*($C$52:$C$190<>"")))}
(curly brackets are indicating array functions)
The formula works without being array function - at least in 6.3.0.3…
I didn’t write all the equation in the cell, it is =INDIRECT(“c”&(SUMPRODUCT(MAX(ROW($C$52:$C$190)*($C$52:$C$190<>""))))) but it returned the value in the last occupied cell in column C. The equation in cells c52 to c190 is =IFERROR(IF(B57="","",IF(B57<Minimum,C56-(AgreedAmount-Minimum)-(Minimum-B57)*Penalty,C56-(AgreedAmount-B57))),"") .
I just wanted to know how the sumproduct(max… part of the equation worked. It is working and gives me the last number I needed.
Thanks for the quick reply
In array mode (forced by using SUMPRODUCT), ROW($C$52:$C$190)*($C$52:$C$190<>"") produces a vector of values: for each cell in range $C$52:$C$190, it takes its row number (using ROW function), and multiplies by a boolean value - the result of checking if the cell’s value isn’t empty. A result of a boolean expression is either TRUE or FALSE - and those are simply numbers 1 or 0, so multiplying row number by TRUE gives the initial number, while multiplying row number by FALSE gives 0. Since all row numbers are positive integers, getting MAX of a vector with some values 0 (where cells were empty), and some are row numbers, gives maximal non-empty row number.
SUMPRODUCT takes a single number in the end, namely the result of MAX, which is already the max row number. It has nothing to add or multiply, so it simply returns the number. It is needed to force array mode here.
Thanks for the answer. I think it is a little bit above my pay grade to figure it out completely but I will work on the <>"" and sumproduct combo more when my head stops spinning.
You can close this question or do I do that?
Anyway, thanks again.
Larry
Using SUMPRODUCT for me is hiding what is happening: I’d write {=MAX(ROW($C$52:$C$190)*($C$52:$C$190<>""))}. It seems to me like some kind of a hack to misuse SUMPRODUCT just as another indicator of “use an array function for the rest of the formula”, but may be that there a cases (here using INDIRECT) requiring this.
So if row c52 to c54 has a number, all will return a 1 and the rest a 0 in the ROW multiplication. Then the MAX function stops at row c54 and returns an answer of 54? How does the sumproduct work since the sums of the 1’s from c52 to c54 would only be three?
Is this an answer to your question? or why did you use a field which is labeled “Propose your solution”?
This isn’t a forum, but an Ask site, where each question might have several answers, and the best answer is brought to top, unlike in forum, where each post is kept in its position chronologically.