Ask Your Question
0

How this works (=SUMPRODUCT(MAX(ROW($C$52:$C$190)*($C$52:$C$190<>"")))) [closed]

asked 2019-08-07 17:09:50 +0200

LarryH gravatar image

updated 2020-07-28 20:50:59 +0200

Alex Kemp gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-28 20:51:47.627882

Comments

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)

Opaque gravatar imageOpaque ( 2019-08-07 17:12:44 +0200 )edit

The formula works without being array function - at least in 6.3.0.3...

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-07 17:34:50 +0200 )edit

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)< em="">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

LarryH gravatar imageLarryH ( 2019-08-07 17:40:05 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2019-08-07 17:40:51 +0200

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.

edit flag offensive delete link more

Comments

1

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-07 18:05:25 +0200 )edit

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

LarryH gravatar imageLarryH ( 2019-08-07 18:14:11 +0200 )edit

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.

Opaque gravatar imageOpaque ( 2019-08-07 18:17:22 +0200 )edit
0

answered 2019-08-07 17:59:08 +0200

LarryH gravatar image

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?

edit flag offensive delete link more

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-07 18:01:46 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2019-08-07 17:09:50 +0200

Seen: 168 times

Last updated: Aug 07 '19