Problems referencing dates with the MATCH formula

  • Thread starter Thread starter Harry Flashman
  • Start date Start date
H

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
 
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
 
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
Click to expand...

Thank you very much for answering the first part of my question. Yes
that did the trick.
 
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
 
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 -
Click to expand...

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?
 
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
 
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 -
Click to expand...

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?
 
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
 
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 -
Click to expand...

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.
 
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
 
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:
Click to expand...

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
 
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
 
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
 
Back
Top