Explanation on formula

Greemo

New Member
Joined
May 22, 2014
Messages
42
Hello, I would just like an explanation on what is going on behind the formulas I have set up. I wanted to set up a formula that would return the last row number in a given cell range. I used =MAX(ROW(A1:B3). however, this formula still returns the smallest row number (returns value of 1), in my specified range (A1:B3), even though I tried to use the MAX formula.

AB
1
2
3

Why is it that, when I change my formula to =SUMPRODUCT(MAX(ROW(A1:B3))), it returns a value of 3? This is the right answer I need I just would like to understand why this is happening.

Greemo


edit: I am using Excel 2013
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Perhaps if you put =ROW(A1:A3) and see the result you will understand better. Also put the 1,2,3 in A5:A7 and try the formula =ROW(A5:A7) and see the result.
 
Upvote 0
Hi,

You can debug formulas. Go to the Formulas tab and look for Evaluate Formula in the Formula Auditing section.

Another way is to use F9.

If you highlight the ROW(A1:B3) part of the formula and hit F9 you will see it change into: {1;2;3}
That is an array of 3 numbers separated by semi-colons. So, they are on separate rows. Hit the Cancel button (X) to revert.
If you drag that formula down the numbers 1, 2 and 3 will appear in separate rows.

If you are expecting the MAX to find 3 then you will need to enter the formula as an array formula by hitting Ctrl + Shift + Enter when you enter it.

I hope that helps.
 
Upvote 0
I used =MAX(ROW(A1:B3). however, this formula still returns the smallest row number (returns value of 1), in my specified range (A1:B3), even though I tried to use the MAX formula.
The problem is the way you committed the formula. It is an array formula and must be committed using CTRL+SHIFT+ENTER and not just Enter by itself.



Why is it that, when I change my formula to =SUMPRODUCT(MAX(ROW(A1:B3))), it returns a value of 3? This is the right answer I need I just would like to understand why this is happening.
The SUMPRODUCT formula was designed to process arrays automatically and, as such, does not need to be committed using the special keystroke.
 
Upvote 0
Wonderful! Thank you for the explanation and new knowledge RickXL and Rick Rothstein.

Greemo
 
Upvote 0
Thanks Aladin,

Understood, but I am still intrigued. Why is that?

Greemo

That brings up an additional cost, that is, invoking a function incurs always an associated cost, control+shift+enter presumably does not. Moreover, such usage in many cases still require control+shift+enter. Finally, such mixes up the programming logic with matchs:

SUMPRODUCT(MAX(...))

>>

SUMPRODUCT(7)

>> 7

That is, SUMPRODUCT sums 7 to 7.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top