HELP! Formula

Star, the "goofy" formula for values in row 1
would be...

=INDEX(1:1,MATCH(9.99999999999999E+307,1:1))

...I hope you won't have more than 256 values;
otherwise, you're gonna run out of columns!

Hey, how's that formula working?

Sometimes this stuff makes my head hurt!
I was thinking along the lines
=max(1:1)
to get the last day, and
=sum(row#:samerow#)
to get the sums of the other rows,
what am I missing?

Re: Hey, how's that formula working?

If the values in 1:1 were {10,30,15} then
=MAX(1:1) would return an index of 2 rather than 3.
=MATCH(9.99999999999999E+307,1:1) is a little
trick that Aladin showed me; however, he prefers
to use =MATCH(MAX(1:1)+1,1:1). I prefer to use
the largest constant that Excel can accept rather
than employing a function call and an operator.
Although it's not documented very well in the Help
files MATCH will return the index of the last
value in a range if you don't use an exact match
(3rd argument is 0 or FALSE) and the value you're
searching for is greater than any of the values in
the range. Both of the formulas will fail if
9.99999999999999E+307 is included as a value in
the range and isn't the last value. Then of course
there's always the risk that this "undocumented
feature" could "disappear" in a future release of
Excel. So, caveat emptor!!! : )

Re: Hey, how's that formula working?

Mee too, although I sometimes used constants like 1.0E+25 (or +30).
That MAX-bit was (anyway intended as) a descriptive note.

I believe there are 4 choices:

[1] Use Excel's largest contant directly in the formulas.
[2] Create a defined name that refers to Excel's largest constant (= 9.99999999999999E+307). [ Excel should have things like this already built-in, as you suggested earlier. ]
[3] Have always an intro sheet for each spreadsheet model that you make where one cell contains this largest value & give that cell a name.
[4] Use that (a bit costly) MAX-bit in the formulas (towards which a certain breed of programmers would be biased).

I'd prefer [2] or [3] I think.

We must not forget also the fact that this "trick" should be applied to columns or rows that house numeric data. It should not be used on data consisting solely of text values.

:Then of course

Yep.

Thanks Mark, Thanks Aladin

That's a useful undocumented feature. Thanks for taking the time to explain it to me.