Lookup-value in HLOOKUP?
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.
|