I have a sheet with lots of year to date data by month, the last column on
the right, which is AN, contains the abbreviated name of the month for each
row, formatted as m to show JAN, FEB,etc. This sheet is updated daily for
year to date info. I am trying to figure out an average month to date, so am
trying to figure out how to assign my JAN(in column AN) as the number of
months YTD. So for instance in this months column AN, it displays JUL next
to each row of information. in AO, I would like it to show 7 months current
YTD, then next month show 8 and so on. That way I can use that cell to
figure my average. How would I write a formula to do that?

Tasha wrote...
....
. . . So for instance in this months column AN, it displays JUL next
to each row of information. in AO, I would like it to show 7 months
current YTD, then next month show 8 and so on. . . .
....

One way,

=MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun","J ul","Aug",
"Sep","Oct","Nov","Dec"},0)

Harlan Grove wrote...
....
One way,

=MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun"," Jul","Aug",
"Sep","Oct","Nov","Dec"},0)

And a simpler way,

=MONTH(AN2&"-1")

That worked perfect!!! Thank you thank you!!!

"Harlan Grove" wrote:

Tasha wrote...
....
. . . So for instance in this months column AN, it displays JUL next
to each row of information. in AO, I would like it to show 7 months
current YTD, then next month show 8 and so on. . . .
....

One way,

=MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun","J ul","Aug",
"Sep","Oct","Nov","Dec"},0)

Always love the shorter ways :)
Thanks!!!

"Harlan Grove" wrote:

Harlan Grove wrote...
....
One way,

=MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun"," Jul","Aug",
"Sep","Oct","Nov","Dec"},0)

And a simpler way,

=MONTH(AN2&"-1")

Harlan,

I know that you will have a reason for it so can I ask you why:

=MONTH(AN2&"-1")


When =MONTH(AN2&"0"), (or any other number from -99 to 99), seems to do the
same thing?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Harlan Grove" wrote in message
ups.com...
Harlan Grove wrote...
...
One way,

=MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun", "Jul","Aug",
"Sep","Oct","Nov","Dec"},0)

And a simpler way,

=MONTH(AN2&"-1")

"Sandy Mann" wrote...
I know that you will have a reason for it so can I ask you why:

=MONTH(AN2&"-1")

When =MONTH(AN2&"0"), (or any other number from -99 to 99), seems
to do the same thing?
....

And they don't need to be quoted. Wouldn't have occurred to me that
Excel would treat, e.g., "JUL0" as a valid date string.

Thank you Harlan.

The earliest reference that I can find in Google to it being used is Aladin
Akyurek in February 2002 but I find that the new Google format is not a good
as it used to be.

--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Harlan Grove" wrote in message
oups.com...
"Sandy Mann" wrote...
I know that you will have a reason for it so can I ask you why:

=MONTH(AN2&"-1")

When =MONTH(AN2&"0"), (or any other number from -99 to 99), seems
to do the same thing?
...

And they don't need to be quoted. Wouldn't have occurred to me that
Excel would treat, e.g., "JUL0" as a valid date string.