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")

 

[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]

That returned a zero.

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

 

[Bob Phillips]

Note the bit about an array formula.

--
HTH

Bob Phillips

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

 

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

 

[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

 

[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]

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]

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]

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

 

[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]

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

 

[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]

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]

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]

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]

Try subtracting the offset: (array entered)

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

Biff

 

[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)