Formula to find Max number in a column. Explanation please!

[zookeepertx]

Hello!

I got this formula somewhere - I think I found it online - and it does what I need it to do, but I can't figure out WHY it works! I need to incorporate it into a larger formula, but I don't know the correct syntax, since I don't get it in the first place, LOL!

I have a LONNNGG column of numbers and I need to figure out what row is the last occupied row. Trouble is, there may be some empty rows as well as the fact that there will be more rows added to the end.

Anyway, here's the formula - It looks pretty simple, so it irritates me that I can't wrap my head around it.

=MAX(MATCH(E1+306,A:A,1))

I know what MAX does and I've used MATCH in other formulas, but where the heck does 306 come from?? Is it maybe just a random number that whoever wrote the formula came up with? Or might it be a number that's specific to the worksheet they were working on and it just happens to work on my small sample sheet? It that's the case, and my actual sheet might be anywhere from 10 to 6,000 rows or more, I assume that, at some point, the formula will get "broken".

I really appreciate any "translation" to plain English that someone here can offer!

Jenny

[jeffreybrown]

Hi Jenny,

Maybe updating your title to the forum rules would help!!!

[zookeepertx]

Hi Jeff,

I'm not sure what's wrong with it. My problem is that I don't know why the formula does what it does and I need to know so that I can use the formula in other situations.

Jenny

[jeffreybrown]

Hi Jenny,

That's somebodies concocted abreviation for a big number in Excel.

The term "BigNum", which stands for "Big Number", is used to represent the largest allowed positive number in Excel, which is 9.99999999999999E+307

You will see all sorts of variations, and this person happened to choose E1+306.

Maybe this site will help

[zookeepertx]

Hi Jeff,

I've been covered up in work and haven't gotten back here till now.

So, I gather that you can't actually use the phrase "BigNum" in a formula, right? It didn't work when I tried it (which doesn't mean it won't work; just means I couldn't get it right)

Jenny

[TMS]

I think, in this case, the MAX is redundant.

[TMS]

Just use the examples you've seen, like

=(MATCH(9.9E+306,A:A,1))

Note: the MAX is redundant here

[zookeepertx]

Hi TMS,

Thanks for the clarification!
Yes, I removed the "MAX" with no ill effects, LOL! That makes it look cleaner; thank you!

Jenny

[FlameRetired]

Using the phrase "BigNum" in formula means that a large value is defined and named "BigNum" in Name Manager.

[zookeepertx]

Hi Dave,

Ah, I see; that makes sense!

Thank you!

Jenny

[FlameRetired]

You are welcome. Glad to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[zookeepertx]

Hi again, Dave.

Darn, I KNEW I was forgetting something, LOL! I'll mark it SOLVED right now.

Jenny

[TMS]

You're welcome.

Either way, the formula only returns the row number of the highest row containing a numeric value, not the value. You could have a similar formula, MATCH("zzzzzz", A:A, 1), to find the highest row number of a text/alphanumeric string. Under those circumstance, you WOULD use MAX if the column contained a mixture of numeric and alphanumeric values. And you'd need to use INDEX with the row number returned to get the actual value.

[zookeepertx]

Hi TMS,

No, this is perfect! All I need is the row number; I don't care about the value in the cell. I just need the row number to use in another formula to establish the range I'm working with and that range changes as rows are added or taken away.

[FlameRetired]

Thanks Jenny.