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

[James Fullmer]

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.

[Don Guillett]

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.

[Bob Phillips]

=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.

[James Fullmer]

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.
>
>
>

[Bob Phillips]

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.
> >
> >
> >

[James Fullmer]

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.
> > >
> > >
> > >
>
>
>

[Peo Sjoblom]

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.
>> > >
>> > >
>> > >
>>
>>
>>

[Biff]

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.
>> > >
>> > >
>> > >
>>
>>
>>

[Peo Sjoblom]

Yes, you need to change it in all 3 places in the formula to H3:H33

--

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:E686EF4B-696A-488B-A3FB-D5FA7AE31BB6@microsoft.com...
>I would like to believe you both, but my eyes are telling me something
>else.
> Maybe I am using the formula incorrectly, I need to change the A1:A1000 to
> the vector that I use, in this case H3:H33 for all three instances of it
> in
> the formula correct?
> They are not text zero's, but they are the result of a formula, would that
> make any difference?
>
> "Peo Sjoblom" wrote:
>
>> 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.
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
>>
>>

[James Fullmer]

I would like to believe you both, but my eyes are telling me something else.
Maybe I am using the formula incorrectly, I need to change the A1:A1000 to
the vector that I use, in this case H3:H33 for all three instances of it in
the formula correct?
They are not text zero's, but they are the result of a formula, would that
make any difference?

"Peo Sjoblom" wrote:

> 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.
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>

[James Fullmer]

they aren't text, but they are the result of a formula, would that make any
difference?

"Biff" wrote:

> 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.
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>

[Biff]

What's that formula look like? Post it.

Does it contain something that looks like this: "0"

If so, that's a TEXT value.

Biff

"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:3A232389-1A2F-451B-8650-ACEA723E707F@microsoft.com...
> they aren't text, but they are the result of a formula, would that make
> any
> difference?
>
> "Biff" wrote:
>
>> 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.
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
>>
>>

[James Fullmer]

Ok, here is a weird one for you all. I have used the formula in another
workbook that works fine with the formula. However, when I put the formula
into the spreadsheet that I need it to be in, it doesn't work. Is there
anything that would impede it from working other than the numbers being input
as text? I have tried it in columns that have formulas to report the numbers,
as well as numbers that I input myself, and it will not work in the file I
currently am using. It does work under the same conditions in a different
file.

Thanks
James

"Peo Sjoblom" wrote:

> Yes, you need to change it in all 3 places in the formula to H3:H33
>
> --
>
> 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:E686EF4B-696A-488B-A3FB-D5FA7AE31BB6@microsoft.com...
> >I would like to believe you both, but my eyes are telling me something
> >else.
> > Maybe I am using the formula incorrectly, I need to change the A1:A1000 to
> > the vector that I use, in this case H3:H33 for all three instances of it
> > in
> > the formula correct?
> > They are not text zero's, but they are the result of a formula, would that
> > make any difference?
> >
> > "Peo Sjoblom" wrote:
> >
> >> 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.
> >> >> > >
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[James Fullmer]

=INDEX(H3:H33,MAX(IF(H3:H33<>0,ROW(H3:H33))))
The formula works in a seperate workbook under all the criteria that I need
it to work, but not in the workbook that I need it. The numbers are all
format as numbers, not text, so I have no idea why it isn't working.
Thanks
James

"Biff" wrote:

> What's that formula look like? Post it.
>
> Does it contain something that looks like this: "0"
>
> If so, that's a TEXT value.
>
> Biff
>
> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
> news:3A232389-1A2F-451B-8650-ACEA723E707F@microsoft.com...
> > they aren't text, but they are the result of a formula, would that make
> > any
> > difference?
> >
> > "Biff" wrote:
> >
> >> 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.
> >> >> > >
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Biff]

If you want to/can send me the file I'll take a look. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:F79BCB82-A626-46B7-871E-E6A690E47DE0@microsoft.com...
> =INDEX(H3:H33,MAX(IF(H3:H33<>0,ROW(H3:H33))))
> The formula works in a seperate workbook under all the criteria that I
> need
> it to work, but not in the workbook that I need it. The numbers are all
> format as numbers, not text, so I have no idea why it isn't working.
> Thanks
> James
>
> "Biff" wrote:
>
>> What's that formula look like? Post it.
>>
>> Does it contain something that looks like this: "0"
>>
>> If so, that's a TEXT value.
>>
>> Biff
>>
>> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
>> news:3A232389-1A2F-451B-8650-ACEA723E707F@microsoft.com...
>> > they aren't text, but they are the result of a formula, would that make
>> > any
>> > difference?
>> >
>> > "Biff" wrote:
>> >
>> >> 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.
>> >> >> > >
>> >> >> > >
>> >> >> > >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

[James Fullmer]

Thanks for the help, I actually figured out what was wrong. Well, I fixed it
but don't understand it. If the formula doesn't have the range beginning
with the first row, meaning in this case, H1:H33 it won't give anything but a
zero. I tried it in a new worksheet, and if you move the range down one, it
will not work. For some reason the formula will only work with the entire
column up to the field that the formula is in.

Weird
Thanks again for the help

"James Fullmer" wrote:

> Ok, here is a weird one for you all. I have used the formula in another
> workbook that works fine with the formula. However, when I put the formula
> into the spreadsheet that I need it to be in, it doesn't work. Is there
> anything that would impede it from working other than the numbers being input
> as text? I have tried it in columns that have formulas to report the numbers,
> as well as numbers that I input myself, and it will not work in the file I
> currently am using. It does work under the same conditions in a different
> file.
>
> Thanks
> James
>
> "Peo Sjoblom" wrote:
>
> > Yes, you need to change it in all 3 places in the formula to H3:H33
> >
> > --
> >
> > 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:E686EF4B-696A-488B-A3FB-D5FA7AE31BB6@microsoft.com...
> > >I would like to believe you both, but my eyes are telling me something
> > >else.
> > > Maybe I am using the formula incorrectly, I need to change the A1:A1000 to
> > > the vector that I use, in this case H3:H33 for all three instances of it
> > > in
> > > the formula correct?
> > > They are not text zero's, but they are the result of a formula, would that
> > > make any difference?
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > >> 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.
> > >> >> > >
> > >> >> > >
> > >> >> > >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

[James Fullmer]

Thanks for the help, I actually figured out what was wrong. Well, I fixed it
but don't understand it. If the formula doesn't have the range beginning
with the first row, meaning in this case, H1:H33 it won't give anything but a
zero. I tried it in a new worksheet, and if you move the range down one, it
will not work. For some reason the formula will only work with the entire
column up to the field that the formula is in.

Weird
Thanks again for the help

"Biff" wrote:

> If you want to/can send me the file I'll take a look. I'm at:
>
> xl can help at comcast period net
>
> Remove "can" and change the obvious.
>
> Biff
>
> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
> news:F79BCB82-A626-46B7-871E-E6A690E47DE0@microsoft.com...
> > =INDEX(H3:H33,MAX(IF(H3:H33<>0,ROW(H3:H33))))
> > The formula works in a seperate workbook under all the criteria that I
> > need
> > it to work, but not in the workbook that I need it. The numbers are all
> > format as numbers, not text, so I have no idea why it isn't working.
> > Thanks
> > James
> >
> > "Biff" wrote:
> >
> >> What's that formula look like? Post it.
> >>
> >> Does it contain something that looks like this: "0"
> >>
> >> If so, that's a TEXT value.
> >>
> >> Biff
> >>
> >> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
> >> news:3A232389-1A2F-451B-8650-ACEA723E707F@microsoft.com...
> >> > they aren't text, but they are the result of a formula, would that make
> >> > any
> >> > difference?
> >> >
> >> > "Biff" wrote:
> >> >
> >> >> 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.
> >> >> >> > >
> >> >> >> > >
> >> >> >> > >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Biff]

Try subtracting the offset: (array entered)

=INDEX(H3:H33,MAX(IF(H3:H33<>0,ROW(H3:H33)-ROW(H3)+1)))

Biff

"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:8E766164-2837-49E2-89B8-357A11FB75B1@microsoft.com...
> Thanks for the help, I actually figured out what was wrong. Well, I fixed
> it
> but don't understand it. If the formula doesn't have the range beginning
> with the first row, meaning in this case, H1:H33 it won't give anything
> but a
> zero. I tried it in a new worksheet, and if you move the range down one,
> it
> will not work. For some reason the formula will only work with the entire
> column up to the field that the formula is in.
>
> Weird
> Thanks again for the help
>
> "Biff" wrote:
>
>> If you want to/can send me the file I'll take a look. I'm at:
>>
>> xl can help at comcast period net
>>
>> Remove "can" and change the obvious.
>>
>> Biff
>>
>> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
>> news:F79BCB82-A626-46B7-871E-E6A690E47DE0@microsoft.com...
>> > =INDEX(H3:H33,MAX(IF(H3:H33<>0,ROW(H3:H33))))
>> > The formula works in a seperate workbook under all the criteria that I
>> > need
>> > it to work, but not in the workbook that I need it. The numbers are all
>> > format as numbers, not text, so I have no idea why it isn't working.
>> > Thanks
>> > James
>> >
>> > "Biff" wrote:
>> >
>> >> What's that formula look like? Post it.
>> >>
>> >> Does it contain something that looks like this: "0"
>> >>
>> >> If so, that's a TEXT value.
>> >>
>> >> Biff
>> >>
>> >> "James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in
>> >> message
>> >> news:3A232389-1A2F-451B-8650-ACEA723E707F@microsoft.com...
>> >> > they aren't text, but they are the result of a formula, would that
>> >> > make
>> >> > any
>> >> > difference?
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> 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.
>> >> >> >> > >
>> >> >> >> > >
>> >> >> >> > >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

[Bob Phillips]

James,

If you shift the base, you either need to fix the ROW part, say use
ROW(A1:A31), as this is an index to the data, or extend the formula to cater
for it

=INDEX(H3:H33,MAX(IF(H3:H33<>0,ROW(H3:H33)-MIN(ROW(H3:H33))+1)))

--
HTH

Bob Phillips

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

"James Fullmer" <JamesFullmer@discussions.microsoft.com> wrote in message
news:43C53291-9323-41DF-B1BD-F377818307DE@microsoft.com...
> Thanks for the help, I actually figured out what was wrong. Well, I fixed
it
> but don't understand it. If the formula doesn't have the range beginning
> with the first row, meaning in this case, H1:H33 it won't give anything
but a
> zero. I tried it in a new worksheet, and if you move the range down one,
it
> will not work. For some reason the formula will only work with the entire
> column up to the field that the formula is in.
>
> Weird
> Thanks again for the help
>
> "James Fullmer" wrote:
>
> > Ok, here is a weird one for you all. I have used the formula in another
> > workbook that works fine with the formula. However, when I put the
formula
> > into the spreadsheet that I need it to be in, it doesn't work. Is there
> > anything that would impede it from working other than the numbers being
input
> > as text? I have tried it in columns that have formulas to report the
numbers,
> > as well as numbers that I input myself, and it will not work in the file
I
> > currently am using. It does work under the same conditions in a
different
> > file.
> >
> > Thanks
> > James
> >
> > "Peo Sjoblom" wrote:
> >
> > > Yes, you need to change it in all 3 places in the formula to H3:H33
> > >
> > > --
> > >
> > > 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:E686EF4B-696A-488B-A3FB-D5FA7AE31BB6@microsoft.com...
> > > >I would like to believe you both, but my eyes are telling me
something
> > > >else.
> > > > Maybe I am using the formula incorrectly, I need to change the
A1:A1000 to
> > > > the vector that I use, in this case H3:H33 for all three instances
of it
> > > > in
> > > > the formula correct?
> > > > They are not text zero's, but they are the result of a formula,
would that
> > > > make any difference?
> > > >
> > > > "Peo Sjoblom" wrote:
> > > >
> > > >> 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.
> > > >> >> > >
> > > >> >> > >
> > > >> >> > >
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >