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. 
0
6/5/2006 8:13:01 PM
excel 39879 articles. 2 followers. Follow

18 Replies
842 Views

Similar Articles

[PageSpeed] 12

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. 


0
dguillett1 (2487)
6/5/2006 8:24:26 PM
=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.


0
bob.NGs1 (1661)
6/5/2006 8:24:53 PM
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.
> 
> 
> 
0
6/5/2006 8:50:02 PM
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.
> >
> >
> >


0
bob.NGs1 (1661)
6/5/2006 9:28:35 PM
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.
> > >
> > >
> > >
> 
> 
> 
0
6/5/2006 10:04:02 PM
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.
>> > >
>> > >
>> > >
>>
>>
>> 


0
Peo
6/5/2006 10:22:45 PM
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.
>> > >
>> > >
>> > >
>>
>>
>> 


0
biffinpitt (3172)
6/5/2006 10:35:21 PM
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.
> >> > >
> >> > >
> >> > >
> >>
> >>
> >> 
> 
> 
> 
0
6/5/2006 10:39:02 PM
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.
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
>>
>> 


0
Peo
6/5/2006 10:42:39 PM
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.
> >> > >
> >> > >
> >> > >
> >>
> >>
> >> 
> 
> 
> 
0
6/5/2006 10:44:02 PM
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.
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
>>
>> 


0
biffinpitt (3172)
6/5/2006 10:53:09 PM
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.
> >> >> > >
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
6/5/2006 11:14:01 PM
=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.
> >> >> > >
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
6/5/2006 11:20:02 PM
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.
>> >> >> > >
>> >> >> > >
>> >> >> > >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
biffinpitt (3172)
6/5/2006 11:35:01 PM
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.
> > >> >> > >
> > >> >> > >
> > >> >> > >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >> 
> > 
> > 
> > 
0
6/5/2006 11:52:02 PM
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.
> >> >> >> > >
> >> >> >> > >
> >> >> >> > >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
6/5/2006 11:52:02 PM
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.
>> >> >> >> > >
>> >> >> >> > >
>> >> >> >> > >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
biffinpitt (3172)
6/6/2006 1:37:54 AM
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.
> > > >> >> > >
> > > >> >> > >
> > > >> >> > >
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >


0
bob.NGs1 (1661)
6/6/2006 8:15:32 AM
Reply:

Similar Artilces:

Copying data to a blank column
I would like to copy data from 1 column in sheet1 starting at row 12 to the first empty column starting at row 8 in sheet2. sub trythis()'SAS untested sc=2 slr=sheets("sheet1").cells(rows.count,sc).end(xlup).row with sheets("sheet2") dlc=.cells(1,columns.count).end(xltoleft).column+1 sheets("sheet1").cells(12,sc).resize(slr) copy .cells(8,dlc) end with end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Ed Davis" <ed.davis1@verizon.net> wrote in message news:6F9BC9EC-E48E-42CC-84D8-01C007B0C6E8@micr...

Adding floating point numbers
hi the value 0.1 in the computer memory is not equal 0.1 so if u add it 10 times the result is diffrent then 1.0: double value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result<1.0 BUT: result=value*10; //result==1.0 WHY??? another example: float value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result==1.0 WHY??? can anyone explain it? thx On Thu, 28 Jun 2007 10:18:05 -0700, rsobies <rsobies@discussions.microsoft.com> wrote: >hi > >the value 0.1 in the computer memory is not equal 0.1...

Getting rid of "fax" numbers when sending e-mail
I use Outlook XP for e-mail and also for contacts. I use my "Contacts" folder as my address book. Whenever I send an e-mail, Outlook always asks me to choose which address I want for the contact, and it brings up both the e-mail address and the fax number. So I have to go through an extra step almost every time of choosing which I want. Is there any way to stop it from bringing up the "Fax" number? See http://www.slipstick.com/contacts/nofax.htm Lee Daum wrote: > I use Outlook XP for e-mail and also for contacts. I use my > "Contacts" folder as my add...

phone numbers
Hello: You know how you cannot integrate employees into Employee Maintenance in Integration Manager if the employee's phone number has parentheses and dashes in it? Well, I found a great TechKnowledge article that walks you through how to replace the parentheses and dashes and successfully integrate. But, if you have one employee in your integration file that does not have a phone number, then Integration Manager throws out the employee record altogether and says "Invalid use of Null: 'Replace'". So, I tried the code below. I added some lines to the TechKnowledg...

Input from remote 10-key and laptop numbers very slow
The last few days I've experience a delay when inputting numbers into Excel 2000 worksheets. When I type in the numbers, they slowly enter themselves, one at a time pausing between each number. This is occurring both with the remote 10-key for the laptop and the number keys on the laptop. This is occuring in all files, no matter how big or small. I wouldn't say I have any overly complicated workbooks, mostly basic functions. I have had much bigger and more complicated before without a problem. When using the 10-key or the number keys anywhere else, Word, internet, e...

How do i number a list of data in excel 2003?
I was shown once, but I can't remember how it was done. I have a list of 5000 things and I need to number then 1-5000, but don't want to go through all 5000. I have seen it done where you type the first few like 1-7 and then use a shortcut and drag it down the rest of the list and the numbers will populate. Please help! You can try something like this: =IF(B1="","",COUNTA($B$1:B1) copy down....or if there are no gabs in your list simply double click on the lower right corner of the cell. HTH JG -- pinmaster -------------------------------------------------...

Cell Number Format includeing other Cell Value
Hello, I needed to create some conditional number format - to say so... My goal: Cell A1 content is "m2" as I Enter in cell A2: "10" -> the output to cell A2 should be "10 m2". Cell A1: "Dollars" -> A2: 200 -> shown "200 Dollars"... and so on.... Is that possible in Excel? (don't know VBA - yet :DD) Any help is appreciated! BR, Daniel How about just use a third cell: =a2 & " " & a1 daniel_of_vienna wrote: > > Hello, > > I needed to create some conditional number format - to say so... > M...

I have 5,00 images that need renamed to part numbers
I have 5,000 images I have to change to current name to a part # found on an excel spreadsheet. How do I change the actual name (not one at a time) but globally with these part #'s? On 2/1/10 4:01 PM, Elizabeth wrote: > I have 5,000 images I have to change to current name to a part # found on an > excel spreadsheet. How do I change the actual name (not one at a time) but > globally with these part #'s? This seems like something that you could do with VBA as long as there is a way to identify the new name from the old name (is the old name listed in the Excel ...

What is the formula to convert a whole column containing date to text?
What is the formula to convert a whole column of date to text? If I am to do it cell by cell, I need to press F2 then put a --> ' in front of the date. Is there a way I can do it with formula? I have tried CONCATENATE to combine column a which contains ' with date 12/31/03. Column A Column B ' 12/31/03 =CONCATENATE(A1,B1) However, the end result I got is as follow. '37986 instead of '12/31/03 Please help! Hi Caine one way: =TEXT(A1,"mm/dd/yyyy") where A1 stores your date value. Copy this down ...

Check value range, then return spesific number?
Hi. I want to implement a function in my spreadsheet, that gives me this: When I enter a number into a cell, another number shows in another cell, based upon the number first entered. A bit more spesific: The idea is to calculate the number of instructors needed for a week end seminar. 1 participant requires 1 instructor. Same for 2 participants, and 3. 4, 5 and 6 participants requires 2 instructors. 7, 8 and 9 participants requires 3 instructors. And so on. For every 3 participant, we apply 1 instructor. So, i.e. if I enter the number 5, i want the number 2 in the cell below. I trie...

eliminate future zeroes
How can I show only valid data to date and not future data not yet entered and considered by Excel as zeroes Hi, Future data cells should be empty. If they contain formula then the result for empty data should be NA() rather than "". Charts treat text values, which "" is, as zero. NA() will only stop the data marker being plotted. The line will be interpolated between valid data points. But as your future points will not contain any valid points the line will stop. Cheers Andy Bryan wrote: > How can I show only valid data to date and not future data not yet enter...

Autoflow, columns and multiple pages
I'm using Publisher 2000 and here's something I've never been able to figure out; how do I set up a multiple page document, each page with 2 columns and copy text so that Publisher will automatically flow into the columns but link the columns together for multiple pages? Or is that even possible? Thanks in advance! MV >-----Original Message----- >I'm using Publisher 2000 and here's something I've never been able to >figure out; how do I set up a multiple page document, each page with 2 >columns and copy text so that Publisher will automatically flow int...

how to change numbers into words,
In excel how to change numberical data into english words, many times currency figures are required to be stated into english words as well so what is the formula for this problem, for example for US$ 5400 : $ Five thousand four hundred is the conversion into words. See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP (remove nothere from the email address if mailing direct) "Mukesh Dhoot" <Mukesh Dhoot @discussions.microsoft.com> wrote in message news:B59710A8-2377-4CF5-98B0-229933A3DE33@microsoft.com... > In excel how to change numberical data into e...

entering 0 to a range of data in a column
How do i Prefix 0 to all the data in a perticular data. Eg. I have different No. like 1234, 3432, 3453 etc in a column and want to see that it shows up as 01234,03432,03453 If it is just for display just use a custom format like 00000, if you need 5 characters in the cell you can use a help column and then use =TEXT(A2,"00000") copy down, edit>paste special as values in place, delete original column -- Regards, Peo Sjoblom "Manoj Nair" <Nair,Manoj@kuwait.army.mil> wrote in message news:90A3390A-3C10-4967-A639-80C0680C7E45@microsoft.com... > How do i Pre...

Line chart zero values
I am trying to do a automated line chart that updates as users input over the next 12 weeks... the chart is consisting of 12 weeks on the x axis and % on the y. The problem im having is the values in the table that the chart is picking up from are formulas... but if the formula result is zero or ""(blank) I want the line chart to ignore it... rather than plot zero values making the line drop when a nil value or blank... for rest of the whole 12 weeks...? eg week 1 = 10% week 2 = 11% week 3 = I want the line to stop at week 2...? but because to work out the % is a formula...

RECEIVED & SENT Column Confusion
I see you can display the SENT column optionally in Outlook 2003. But strangely, the SENT column shows a time newer than the RECEIVED column. Like a message will say SENT 9:59 PM & RECEIVED 9:31 PM. What's up with that? Thanks, BTJustice one of the mail servers or computers has the wrong time. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions...

I have a column of numbers, some negative, some positive
I have some calulations in my worksheet. They are done twice, if my number is positive or negative. I need to fill the cell next to my number with the calulation that correspondes with the + or - of my number. if A1 + if A1 - A1 B1 C1 D1 -10 7 12 A2 B2 C2 D2 14 4 6 I need the 12 to appear in B1. and the 4 to appear in B2. Thanks In B1 enter =IF(A1<0,D1,C1) Copy down. Gord Dibben M...

Sum a column if two criteria are met
I need a formula that sums column C for each "Loc" by "Item"? A B C LOC Item WA Txn Value 1241 R3331 595.20 1241 R3334 595.20 1241 R3334 1,488.00 1242 R1400 908.46 1242 R1400 908.46 1242 R3334 1,488.00 1242 R3334 1,190.40 1243 R1400 908.46 1243 R3334 297.60 -- swestberry Hi, Try this =SUMPRODUCT((A2:A20=1241)*(B2:B20="R3334")*(C2:C20)) You could use cell ref's for the lookup values instead of having them embedded in the formula =SUMPRODUCT((A2:A20=D1)*(B2:B20=D2)*(C2:C20)) -- Mike ...

Show column header in addition to min/max result
Hello everyone Every month I work out the max and min for a range of income types over the last 24, 18, 12 and 6 months. As well as displaying the max and min result, is there a way to also select which of the months was the max and which was the min so it can be displayed alongside the value? The month is the column header. You didn't post your formula. Have a look in the help index for INDEX -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Tabbi" <Tabbicat22@gmail.com> wrote in message news:bbc50392-0d13-4020-97df-98c238352b4d@e25g200...

Excel Changes last-Modified Date
Is there a way to stop Excel from changing the last-modified date of every file that is opened (even when no changes are made and save is never pressed)? This is really screwing us up in the office because we are looking through a ton of old files for one that was last-modified on a certain date. When we open a suspect file it CHANGES THE DATE!!!! Make it stop. Thanks. It is only temporary, if you don't save it it reverst back when you close it. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Jerry Baker" <jerry@nodomain.invalid> w...

Database Diagram Column notes
Is there any way to display the notes from the column properties in my drawing? ...

Scatter chart with columns instead of points
I need to create a chart showing the number of students who received each numerical grade on test. The spacing between grades is not equidistant (ex: 65, 70, 90, etc.) and I need to show the true spacing on the x-axis. So I chose a scatter chart because the other charts make the points of equal distance on the x-axis. The trouble is I need to represent the data as columns so if two students scored a 65 and eight scored a 90, the 90 column would much higher. Is there a way to add vertical columns to a scatter chart? Put your grades in a column, and include the grades which had none. ...

Adding spaces between numbers in a cell that contains a full address
Hello, Sample data: 3-764 Neighbourhood Cir 39 Frostbite Lane 3938 Stardust Drive 4 Jones Lane I have a spreadsheet that contains addresses in a column such as the ones above. What I need to do is separate the numbers in the addresses in one of two ways. Either would be ok. The first is (and better for me) would be so that the number portion of the address would have spaces between every number, and then the rest of the addresses in the same cell. For example, "3-764 Neighbourhood Cir", would become "3 - 7 6 4 Neighbourhood Cir". The second would be to split the num...

Can Leading Zero Be Hidden?
I have a number field (double) where it would be nice if Access didn't show a leading zero. Decimal Places is set to "Auto", and that is good, except for the leading zero. Format is currently blank. Access 2002. Thanks, croy On Tue, 23 Mar 2010 06:36:01 -0700, croy <croy@invalid.net> wrote: >I have a number field (double) where it would be nice if >Access didn't show a leading zero. Decimal Places is set to >"Auto", and that is good, except for the leading zero. >Format is currently blank. Access 2002. > >Thanks, >cro...

Budget Numbers...Where from?
When I look at my Budget, I see an amount in the "Budgeted" column in the "Special :Debt" catagory that I don't recognize. It is a different amount each month. Can anyone tell me where Money gets this amount from? "Jimbob" <Jimbob@discussions.microsoft.com> wrote in message news:C3506374-D004-47E3-B010-61F1EA695A88@microsoft.com... > When I look at my Budget, I see an amount in the "Budgeted" column in the > "Special :Debt" catagory that I don't recognize. It is a different amount > each month. Can anyone tell me wher...