Re: Problems referencing dates with the MATCH formula

Okay, with that layout this formula will return the column ID as a
letter (or letters):

=IF(MATCH(B2,D$1:BD$1,0)+3>26,CHAR(INT((MATCH(B2,D$1:BD$1,0)+2)/
26)+64),"")&CHAR(MOD(MATCH(B2,D$1:BD$1,0)+2,26)+65)

(I'm sure there must be a better way, but this works for now!!)

So, your average formula will be (in C2):

=AVERAGE(INDIRECT("D"&ROW(C2)&":"&IF(MATCH(B2,D$1:BD
$1,0)+3>26,CHAR(INT((MATCH(B2,D$1:BD$1,0)+2)/
26)+64),"")&CHAR(MOD(MATCH(B2,D$1:BD$1,0)+2,26)+65)&ROW(C2)))

I've had to use the ROW function in order for the row reference to
increment as you copy the formula down - it doesn't matter what
reference is actually in there, though I've used C2.

So, copy this down and it should give you what you want - obviously,
if you have no values in columns D to BD then you will get #DIV/0
error, and if you have no date in column B you will get the #N/A
error, so you might want to trap these before copying down.

Hope this helps.

Pete


On May 13, 11:02 am, Harry Flashman <flashdav...@xxxxxxxxx> wrote:

I am sorry Pete. You have actually been a big help and you have helped
improve my understanding a lot.
I tried to keep my example simple. Also I made a typo in my first
post.
"In Column D to A I have the product sales by month." It should not
have ben D to A.
I meant "Column D to Z I have product sales by month".
In my real speadsheet I actually have Column D to BD, that is months
Mar-08 to Dec-03.
Column A contains the names of the products.
Column B contains the months the product was first sold (format mmm-
yy)
Column C contains the average monthly sales from the time that the
specific product was first sold. The first date of sale varies from
product to product.
Column D to BD is the months (format mmm-yy)

Put simply I am calculating the average sales of each product. But I
need something a little more sophisticated than just =AVERAGE(D:BD)
because not all products went on sale for the first time at the same
time.

The array formula you provided would be okay except that in some
months a product may sell zero. I still want to include that zero in
my average
.

Relevant Pages

  • Re: Problems referencing dates with the MATCH formula
    ... I meant "Column D to Z I have product sales by month". ... Column B contains the months the product was first sold (format mmm- ... Column D to BD is the months (format mmm-yy) ... months a product may sell zero. ...
    (microsoft.public.excel)
  • Re: DevRel--How far along has shipping progressed?
    ... "Brad White" wrote ... > They did say that they had about ~two weeks left of shipping to go. ... Okay, thanks. ... I've emailed my SA sales rep and asked him to check on it. ...
    (borland.public.delphi.non-technical)
  • Re: Abel reels?
    ... sales in the near future? ... Okay got me looking. ...  Only thing I could find was an blog talking ... Andrew Madoff is their CEO and the lawsuits have yet to be filed. ...
    (rec.outdoors.fishing.fly)
  • Re: [OT: combating PC idiots] MERRY CHRISTMAS.. its okay to say it!
    ... The idea that it's not okay for stores to put up ... > "Christmas" displays is retarded, ... more in sales, that's what they'll do. ...
    (rec.sport.football.college)
  • X-Men First Class
    ... Why is this title okay but not Byrne's Hidden Years? ... to canceled despite good sales, you claimed that you didn't want any title ... Ultimate Universe doesn't?) ... on professional envies? ...
    (rec.arts.comics.marvel.xbooks)