Last Number in a column to that is not equal to zero

I have an inventory worksheet that I am having trouble with. The total daily
inventory column updates daily. At the end of the column, I want a formula
that gives the most recent inventory number. For instance

1 15,358
2 15,358
3 19,520
4 19,520
5 19,693
6 0
7 0
8 0
9 0
10 0
11 0

The final field in the inventory column would read 19693.
There will always be a whole number for inventory, so the zeros will be
replace with the number once the daily inventory is completed.

one thing you can try. If you have a header row add +1
=COUNTIF(A:A,">0")
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com...
>I have an inventory worksheet that I am having trouble with. The total
>daily
> inventory column updates daily. At the end of the column, I want a formula
> that gives the most recent inventory number. For instance
>
> 1 15,358
> 2 15,358
> 3 19,520
> 4 19,520
> 5 19,693
> 6 0
> 7 0
> 8 0
> 9 0
> 10 0
> 11 0
>
> The final field in the inventory column would read 19693.
> There will always be a whole number for inventory, so the zeros will be
> replace with the number once the daily inventory is completed.

=INDEX(A1:A1000,MAX(IF(A1:A1000<>0,ROW(A1:A1000))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com...
> I have an inventory worksheet that I am having trouble with. The total
daily
> inventory column updates daily. At the end of the column, I want a formula
> that gives the most recent inventory number. For instance
>
> 1 15,358
> 2 15,358
> 3 19,520
> 4 19,520
> 5 19,693
> 6 0
> 7 0
> 8 0
> 9 0
> 10 0
> 11 0
>
> The final field in the inventory column would read 19693.
> There will always be a whole number for inventory, so the zeros will be
> replace with the number once the daily inventory is completed.

That returned a zero.

"Bob Phillips" wrote:

> =INDEX(A1:A1000,MAX(IF(A1:A1000<>0,ROW(A1:A1000))))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
> news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com...
> > I have an inventory worksheet that I am having trouble with. The total
> daily
> > inventory column updates daily. At the end of the column, I want a formula
> > that gives the most recent inventory number. For instance
> >
> > 1 15,358
> > 2 15,358
> > 3 19,520
> > 4 19,520
> > 5 19,693
> > 6 0
> > 7 0
> > 8 0
> > 9 0
> > 10 0
> > 11 0
> >
> > The final field in the inventory column would read 19693.
> > There will always be a whole number for inventory, so the zeros will be
> > replace with the number once the daily inventory is completed.
>
>
>

Note the bit about an array formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:F54B8434-76ED-4280-A523-D17DBDE1FA58@microsoft.com...
> That returned a zero.
>
> "Bob Phillips" wrote:
>
> > =INDEX(A1:A1000,MAX(IF(A1:A1000<>0,ROW(A1:A1000))))
> >
> > which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
> > just Enter.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in
message
> > news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com...
> > > I have an inventory worksheet that I am having trouble with. The
total
> > daily
> > > inventory column updates daily. At the end of the column, I want a
formula
> > > that gives the most recent inventory number. For instance
> > >
> > > 1 15,358
> > > 2 15,358
> > > 3 19,520
> > > 4 19,520
> > > 5 19,693
> > > 6 0
> > > 7 0
> > > 8 0
> > > 9 0
> > > 10 0
> > > 11 0
> > >
> > > The final field in the inventory column would read 19693.
> > > There will always be a whole number for inventory, so the zeros will
be
> > > replace with the number once the daily inventory is completed.
> >
> >
> >

I did use the Ctrl+Shift+Enter, and it at least returned zero, but that is
where it ended. And the last number in the column isn't always the largest,
so I don't think the max formula will work.

"Bob Phillips" wrote:

> Note the bit about an array formula.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
> news:F54B8434-76ED-4280-A523-D17DBDE1FA58@microsoft.com...
> > That returned a zero.
> >
> > "Bob Phillips" wrote:
> >
> > > =INDEX(A1:A1000,MAX(IF(A1:A1000<>0,ROW(A1:A1000))))
> > >
> > > which is an array formula, it should be committed with Ctrl-Shift-Enter,
> not
> > > just Enter.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in
> message
> > > news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com...
> > > > I have an inventory worksheet that I am having trouble with. The
> total
> > > daily
> > > > inventory column updates daily. At the end of the column, I want a
> formula
> > > > that gives the most recent inventory number. For instance
> > > >
> > > > 1 15,358
> > > > 2 15,358
> > > > 3 19,520
> > > > 4 19,520
> > > > 5 19,693
> > > > 6 0
> > > > 7 0
> > > > 8 0
> > > > 9 0
> > > > 10 0
> > > > 11 0
> > > >
> > > > The final field in the inventory column would read 19693.
> > > > There will always be a whole number for inventory, so the zeros will
> be
> > > > replace with the number once the daily inventory is completed.
> > >
> > >
> > >
>
>
>

MAX in this formula has nothing to do with the max number, the formula
works, however if a zero is a text zero (0 aligned left with no alignment
chosen) then it will return the zero since all text values are greater than
any number. Believe me the formula works

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:A54F0ECA-BB1F-40AD-A3D2-2C906D25EF25@microsoft.com...
>I did use the Ctrl+Shift+Enter, and it at least returned zero, but that is
> where it ended. And the last number in the column isn't always the
> largest,
> so I don't think the max formula will work.
>
> "Bob Phillips" wrote:
>
>> Note the bit about an array formula.
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
>> news:F54B8434-76ED-4280-A523-D17DBDE1FA58@microsoft.com...
>> > That returned a zero.
>> >
>> > "Bob Phillips" wrote:
>> >
>> > > =INDEX(A1:A1000,MAX(IF(A1:A1000<>0,ROW(A1:A1000))))
>> > >
>> > > which is an array formula, it should be committed with
>> > > Ctrl-Shift-Enter,
>> not
>> > > just Enter.
>> > >
>> > > --
>> > > HTH
>> > >
>> > > Bob Phillips
>> > >
>> > > (replace somewhere in email address with gmail if mailing direct)
>> > >
>> > > "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in
>> message
>> > > news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com...
>> > > > I have an inventory worksheet that I am having trouble with. The
>> total
>> > > daily
>> > > > inventory column updates daily. At the end of the column, I want a
>> formula
>> > > > that gives the most recent inventory number. For instance
>> > > >
>> > > > 1 15,358
>> > > > 2 15,358
>> > > > 3 19,520
>> > > > 4 19,520
>> > > > 5 19,693
>> > > > 6 0
>> > > > 7 0
>> > > > 8 0
>> > > > 9 0
>> > > > 10 0
>> > > > 11 0
>> > > >
>> > > > The final field in the inventory column would read 19693.
>> > > > There will always be a whole number for inventory, so the zeros
>> > > > will
>> be
>> > > > replace with the number once the daily inventory is completed.
>> > >
>> > >
>> > >
>>
>>
>>

Are those values formatted as TEXT?

> the last number in the column isn't always the largest,
> so I don't think the max formula will work.

That doesn't have anything to do with how the MAX function in the formula
works. It's looking for the MAX ROW NUMBER, not the MAX VALUE in the column.

The formula works if the values are numeric. If they're TEXT it will return
the zero as you've experienced.

Biff

"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:A54F0ECA-BB1F-40AD-A3D2-2C906D25EF25@microsoft.com...
>I did use the Ctrl+Shift+Enter, and it at least returned zero, but that is
> where it ended. And the last number in the column isn't always the
> largest,
> so I don't think the max formula will work.
>
> "Bob Phillips" wrote:
>
>> Note the bit about an array formula.
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
>> news:F54B8434-76ED-4280-A523-D17DBDE1FA58@microsoft.com...
>> > That returned a zero.
>> >
>> > "Bob Phillips" wrote:
>> >
>> > > =INDEX(A1:A1000,MAX(IF(A1:A1000<>0,ROW(A1:A1000))))
>> > >
>> > > which is an array formula, it should be committed with
>> > > Ctrl-Shift-Enter,
>> not
>> > > just Enter.
>> > >
>> > > --
>> > > HTH
>> > >
>> > > Bob Phillips
>> > >
>> > > (replace somewhere in email address with gmail if mailing direct)
>> > >
>> > > "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in
>> message
>> > > news:77C2E128-1C80-4D49-AFD8-1E907EB2331E@microsoft.com...
>> > > > I have an inventory worksheet that I am having trouble with. The
>> total
>> > > daily
>> > > > inventory column updates daily. At the end of the column, I want a
>> formula
>> > > > that gives the most recent inventory number. For instance
>> > > >
>> > > > 1 15,358
>> > > > 2 15,358
>> > > > 3 19,520
>> > > > 4 19,520
>> > > > 5 19,693
>> > > > 6 0
>> > > > 7 0
>> > > > 8 0
>> > > > 9 0
>> > > > 10 0
>> > > > 11 0
>> > > >
>> > > > The final field in the inventory column would read 19693.
>> > > > There will always be a whole number for inventory, so the zeros
>> > > > will
>> be
>> > > > replace with the number once the daily inventory is completed.
>> > >
>> > >
>> > >
>>
>>
>>