# HELP! Formula

Posted by Star Hyde on August 31, 2001 5:19 PM

I have a spreadsheet where numbers are added in on a daily basis.Column A is day 1 column B is day two etc.

On a seperate page the SUM is displayed. I also need to display the LAST DAYS entry. What the heck is the goofy formula? Can someone help? Please?

Posted by Mark W. on September 01, 2001 1:48 PM

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!

Posted by Eric on September 01, 2001 7:00 PM

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?

Posted by Mark W. on September 02, 2001 6:08 AM

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!!! : )

Posted by Aladin Akyurek on September 02, 2001 6:42 AM

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.

Posted by Eric on September 02, 2001 9:14 PM

Thanks Mark, Thanks Aladin

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