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

On May 13, 6:26*pm, "Ed Ferrero" <ed@edferrero_remove_.com> wrote:
> 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.

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

On May 13, 9:12*am, Harry Flashman <flashdav...@gmail.com> wrote:
> 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

On May 13, 6:37*pm, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On May 13, 9:12*am, Harry Flashman <flashdav...@gmail.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - 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?

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

On May 13, 9:47*am, Harry Flashman <flashdav...@gmail.com> wrote:
> On May 13, 6:37*pm, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
>
>
> > 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
>
> > On May 13, 9:12*am, Harry Flashman <flashdav...@gmail.com> wrote:
>
> > > 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- Hide quoted text -
>
> > - 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?- Hide quoted text -
>
> - Show quoted text -

On May 13, 7:13*pm, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On May 13, 9:47*am, Harry Flashman <flashdav...@gmail.com> wrote:
>
>
>
> > On May 13, 6:37*pm, Pete_UK <pashu...@auditel.net> wrote:
>
> > > 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
>
> > > On May 13, 9:12*am, Harry Flashman <flashdav...@gmail.com> wrote:
>
> > > > 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- Hide quoted text -
>
> > > - 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?- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - 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?

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

On May 13, 10:26*am, Harry Flashman <flashdav...@gmail.com> wrote:
>
> 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

On May 13, 7:35*pm, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On May 13, 10:26*am, Harry Flashman <flashdav...@gmail.com> wrote:
>
>
>
>
>
> > 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- Hide quoted text -
>
> - 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.

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...@gmail.com> 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