It seems you can't use a formula (eg "0") as the lookup value in VLOOKUP or
HLOOKUP. Is there a way around this? I have month by month sales data for
a few thousand items for 36 months and I am trying to establish how many
months ago was the last sales activity.

Chrisp wrote:
It seems you can't use a formula (eg "0") as the lookup value in VLOOKUP or
HLOOKUP. Is there a way around this? I have month by month sales data for
a few thousand items for 36 months and I am trying to establish how many
months ago was the last sales activity.

Hi Chrisp, Show us how you have your data organized.

Hi,

I have table of 36 months of historical data
-Column headings are month/year in order (oldest to newest left to right)
-Row headings are product codes
-Data is the quantity sold for each product each month

With a few thousand products, my challenge is to find a quick way of
calculating for each product how many months have elapsed since the last sale
- ie how many zeros, if any, at the right hand end of each row. This is to
identify products which may be obsolete.

I thought that using HLOOKUP might have some potential, but I am beginning
to think that this may have to be a macro solution.

Would certainly appreciate any ideas you may have.

Many thanks

Chris P


"smartin" wrote:

Chrisp wrote:
It seems you can't use a formula (eg "0") as the lookup value in VLOOKUP or
HLOOKUP. Is there a way around this? I have month by month sales data for
a few thousand items for 36 months and I am trying to establish how many
months ago was the last sales activity.

Hi Chrisp, Show us how you have your data organized.

Hi Chrisp,

With 36 months in columns B:AK starting in row 2, the following array
formula* placed in a convenient column to the right of your data will
return the right-most column number with a non-zero value (*array
formula -- commit with Ctrl+Shift+Enter):

=MAX(--($B2:$AK2<0)*COLUMN($B2:$AK2))

a quick modification tells you how many right-trailing zeros you have on
each row (again, an array formula):

=37-MAX(--($B2:$AK2<0)*COLUMN($B2:$AK2))

Hope this helps!

Chrisp wrote:
Hi,

I have table of 36 months of historical data
-Column headings are month/year in order (oldest to newest left to right)
-Row headings are product codes
-Data is the quantity sold for each product each month

With a few thousand products, my challenge is to find a quick way of
calculating for each product how many months have elapsed since the last sale
- ie how many zeros, if any, at the right hand end of each row. This is to
identify products which may be obsolete.

I thought that using HLOOKUP might have some potential, but I am beginning
to think that this may have to be a macro solution.

Would certainly appreciate any ideas you may have.

Many thanks

Chris P


"smartin" wrote:

Chrisp wrote:
It seems you can't use a formula (eg "0") as the lookup value in VLOOKUP or
HLOOKUP. Is there a way around this? I have month by month sales data for
a few thousand items for 36 months and I am trying to establish how many
months ago was the last sales activity.
Hi Chrisp, Show us how you have your data organized.

Hi smartin,

My apologies for the delay in replying - had hardware problems.

Your proposal is as effective as it is neat. I had put together a very
clunky solution that involved creating another 36 columns that could be
hidden later, each containing a formula to count blank columns. Your idea is
infinitely better.

Many thanks again,

ChrisP

"smartin" wrote:

Hi Chrisp,

With 36 months in columns B:AK starting in row 2, the following array
formula* placed in a convenient column to the right of your data will
return the right-most column number with a non-zero value (*array
formula -- commit with Ctrl+Shift+Enter):

=MAX(--($B2:$AK2<0)*COLUMN($B2:$AK2))

a quick modification tells you how many right-trailing zeros you have on
each row (again, an array formula):

=37-MAX(--($B2:$AK2<0)*COLUMN($B2:$AK2))

Hope this helps!

Chrisp wrote:
Hi,

I have table of 36 months of historical data
-Column headings are month/year in order (oldest to newest left to right)
-Row headings are product codes
-Data is the quantity sold for each product each month

With a few thousand products, my challenge is to find a quick way of
calculating for each product how many months have elapsed since the last sale
- ie how many zeros, if any, at the right hand end of each row. This is to
identify products which may be obsolete.

I thought that using HLOOKUP might have some potential, but I am beginning
to think that this may have to be a macro solution.

Would certainly appreciate any ideas you may have.

Many thanks

Chris P


"smartin" wrote:

Chrisp wrote:
It seems you can't use a formula (eg "0") as the lookup value in VLOOKUP or
HLOOKUP. Is there a way around this? I have month by month sales data for
a few thousand items for 36 months and I am trying to establish how many
months ago was the last sales activity.
Hi Chrisp, Show us how you have your data organized.