Problems referencing dates with the MATCH formula

[Harry Flashman]

I am finding this a little unpredictable. Sometimes it works fine; for
example:
A B C D
Date Mar-08 Feb-08 Jan-08

If I wish to return the column number from the dates:
Mar-08 =MATCH(A2,$A$1:$D$1) returns the value 2
Feb-08 =MATCH(A3,$A$1:$D$1) returns the value 3
Jan-08 =MATCH(A4,$A$1:$D$1) returns the value 4

However if I expand the reference to include column E, Dec-07 I get
this result:
Mar-08 =MATCH(A2,$A$1:$E$1) returns the value 5 (which is the total
number of cells in the reference).

Can anyone explain why this would be the case?
(The dates are actually 01/03/2008 (UK style) but I have used custom
format to display them as mmm-yy. Also the dates are always the first
of the month.)

Then next part of my question is a little more complex.

In column A I have a product names
In column B I have the first date that this product was sold.
In Column D to A I have the product sales by month.
I would like to use the AVERAGE formula in in column C to calculate
the average sales over time.
However not all products have the same history; some are more recent
than others; products have the value zero in the months before they
appeared.
Therefore if a product has been around since the start the formula
will be =AVERAGE(D2:Z2)
But if the product has only been around for the past three months the
formula will be =AVERAGE(D2:F2)

I would like to use the MATCH formula (or a similar formula) to
replace the second cell reference in my AVERAGE formula. That is, a
nested formula that references the values in column B (the date the
product was first sold) and returns the appropriate column reference.

I hope I have been clear. Can anyone help with either of these
question? I would be eternally grateful.

Harry

 

[Ed Ferrero]

Hi Harry,

Try using =MATCH(A2,$A$1:$E$1,0)

From help;
If match_type is omitted, it is assumed to be 1.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

Ed Ferrero
www.edferrero.com

 

[Harry Flashman]

Hi Harry,

Try using =MATCH(A2,$A$1:$E$1,0)

From help;
If match_type is omitted, it is assumed to be 1.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

Ed Ferrerowww.edferrero.com

Thank you very much for answering the first part of my question. Yes
that did the trick.

 

[Pete_UK]

Try setting the third parameter of the MATCH function to zero, to
ensure that you are looking for an exact match, i.e.:

=MATCH(A2,$A$1:$E$1,0)

In answer to your second question, you can use this array* formula:

=AVERAGE(IF(D2:Z2<>0,D2:Z2))

which will only count non-zero entries in the average.

* An array formula has to be committed using the key combination of
CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - you should not type these yourself.

Another way of avoiding counting the zeros in the average is to use
COUNTIF:

=SUM(D2:Z2)/COUNTIF(D2:Z2,">0")

or if you really want to use a MATCH function to adjust the range of
the AVERAGE function, then you will have to do so within an INDIRECT
function.

Hope this helps.

Pete

 

[Harry Flashman]

Try setting the third parameter of the MATCH function to zero, to
ensure that you are looking for an exact match, i.e.:

=MATCH(A2,$A$1:$E$1,0)

In answer to your second question, you can use this array* formula:

=AVERAGE(IF(D2:Z2<>0,D2:Z2))

which will only count non-zero entries in the average.

* An array formula has to be committed using the key combination of
CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - you should not type these yourself.

Another way of avoiding counting the zeros in the average is to use
COUNTIF:

=SUM(D2:Z2)/COUNTIF(D2:Z2,">0")

or if you really want to use a MATCH function to adjust the range of
the AVERAGE function, then you will have to do so within an INDIRECT
function.

Hope this helps.

Pete











- Show quoted text -

Thank for your advice which I am sure I will have call to use some
time.
In this case I would like to include the zeros. The only zero's I do
not want to include are the zeros that are there because the product
had not yet been released.
I had a feeling that I would need to use the INDIRECT formula in some
way, but I am not sure how to use it. I'll try reading some more,
thank you.

=AVERAGE(D2:INDIRECT(....

Will it look something like the above?

 

[Pete_UK]

It will look something like:

=AVERAGE(INDIRECT("D2:" &
your_match_function_turning_col_number_to_letter & "2"))

As you are only interested in up to column Z, you can use the CHAR
function to convert the match number to a letter.

Hope this helps.

Pete

 

[Harry Flashman]

It will look something like:

=AVERAGE(INDIRECT("D2:" &
your_match_function_turning_col_number_to_letter & "2"))

As you are only interested in up to column Z, you can use the CHAR
function to convert the match number to a letter.

Hope this helps.

Pete





- Show quoted text -

Thank you very much that is a big help. But in fact I actually go up
to column BD. My example was simplified.
Can I still use the MATCH function in someway here?

 

[Pete_UK]

I'm a bit confused about the column references you are using. In your
first example you just mentioned about columns A to E, then you
mentioned a range from D to Z, and now you talk about going up to BD -
that's why I didn't actually give you a formula to do this. If you can
spell out exactly how your data is laid out then I might be able to
quote a formula to do it.

Pete

 

[Harry Flashman]

I'm a bit confused about the column references you are using. In your
first example you just mentioned about columns A to E, then you
mentioned a range from D to Z, and now you talk about going up to BD -
that's why I didn't actually give you a formula to do this. If you can
spell out exactly how your data is laid out then I might be able to
quote a formula to do it.

Pete



- Show quoted text -

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.

 

[Pete_UK]

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

 

[Harry Flashman]

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 <[email protected]> wrote:

You're a brilliant man Pete! Works like a charm.
Thanks for showing me the first part of the formula too, so I could
see how it works.
You have something I can study to so that I can understand it
properly.
Cheers

 

[Harry Flashman]

You're a brilliant man Pete! Works like a charm.
Thanks for showing me the first part of the formula too, so I could
see how it works.
You have given me something I can study to so that I can understand
it
properly.
Cheers

 

[Pete_UK]

Well, thanks for feeding back - I'm glad it did the job for you.

The first part of the formula was the complex bit, so that's why I
gave you that separately - glad you appreciate it, as we are all here
to learn.

Pete