Prev: Date and Time
Next: Totalling Subtotals Less Than 1
From: mjones on 14 May 2010 20:37
Hi All,

I hope someone can help with a tricky formula. Given this type of
sample data:

A B C D E
F G
1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10
2 x
x x
3 x x x x
4 x x
5
x x x
6
7

Looking at row 2 -
In cell A6, find the first (left most) x and return the month-year in
the row above it. This should give Apr-10.
In cell A7, find the last (right most) x and return the month-year in
the row above it. This should give Jun-10.
Then I should be able to determine the formula for rows 3 to 5.

If someone knows this, it will save a lot of people manual entries.

Thanks!

Michele
From: T. Valko on 14 May 2010 21:06
Try these...

For the leftmost "x":

=INDEX(A1:G1,MATCH("x",A2:G2,0))

For the rightmost "x":

=INDEX(A1:G1,MATCH("xxxxx",A2:G2))

--
Biff
Microsoft Excel MVP


"mjones" <michele(a)quality-computing.com> wrote in message
news:cfb66747-3c8b-4d31-9f76-73a89e77155c(a)d3g2000vbr.googlegroups.com...
> Hi All,
>
> I hope someone can help with a tricky formula. Given this type of
> sample data:
>
> A B C D E
> F G
> 1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10
> 2 x
> x x
> 3 x x x x
> 4 x x
> 5
> x x x
> 6
> 7
>
> Looking at row 2 -
> In cell A6, find the first (left most) x and return the month-year in
> the row above it. This should give Apr-10.
> In cell A7, find the last (right most) x and return the month-year in
> the row above it. This should give Jun-10.
> Then I should be able to determine the formula for rows 3 to 5.
>
> If someone knows this, it will save a lot of people manual entries.
>
> Thanks!
>
> Michele


From: mjones on 14 May 2010 22:32
On May 14, 9:06 pm, "T. Valko" <biffinp...(a)comcast.net> wrote:
> Try these...
>
> For the leftmost "x":
>
> =INDEX(A1:G1,MATCH("x",A2:G2,0))
>
> For the rightmost "x":
>
> =INDEX(A1:G1,MATCH("xxxxx",A2:G2))
>
> --
> Biff
> Microsoft Excel MVP
>
> "mjones" <mich...(a)quality-computing.com> wrote in message
>
> news:cfb66747-3c8b-4d31-9f76-73a89e77155c(a)d3g2000vbr.googlegroups.com...
>
> > Hi All,
>
> > I hope someone can help with a tricky formula.  Given this type of
> > sample data:
>
> >        A            B            C           D           E
> > F           G
> > 1  Jan-10    Feb-10    Mar-10    Apr-10    May-10    Jun-10    Jul-10
> > 2                                                x
> > x            x
> > 3                    x             x            x           x
> > 4      x            x
> > 5
> > x            x           x
> > 6
> > 7
>
> > Looking at row 2 -
> > In cell A6, find the first (left most) x and return the month-year in
> > the row above it.  This should give Apr-10.
> > In cell A7, find the last (right most) x and return the month-year in
> > the row above it.  This should give Jun-10.
> > Then I should be able to determine the formula for rows 3 to 5.
>
> > If someone knows this, it will save a lot of people manual entries.
>
> > Thanks!
>
> > Michele

Thank you very much. Your formulas work great on a test worksheet.
Now I just have to determine why they are giving me a #NA - value not
available to the formula or function. There are month/years in all
the appropriate rows. I'm sure I'll work it out. Thanks again.
From: T. Valko on 14 May 2010 22:51
>There are month/years in all the appropriate rows

It looks like x's in the data rows and the date (to be returned) is the in
the header row.

Both formulas are specifically looking for x's in the data rows.

--
Biff
Microsoft Excel MVP


"mjones" <michele(a)quality-computing.com> wrote in message
news:d3427866-4b19-4b04-93d4-2ea730a72ed9(a)z33g2000vbb.googlegroups.com...
On May 14, 9:06 pm, "T. Valko" <biffinp...(a)comcast.net> wrote:
> Try these...
>
> For the leftmost "x":
>
> =INDEX(A1:G1,MATCH("x",A2:G2,0))
>
> For the rightmost "x":
>
> =INDEX(A1:G1,MATCH("xxxxx",A2:G2))
>
> --
> Biff
> Microsoft Excel MVP
>
> "mjones" <mich...(a)quality-computing.com> wrote in message
>
> news:cfb66747-3c8b-4d31-9f76-73a89e77155c(a)d3g2000vbr.googlegroups.com...
>
> > Hi All,
>
> > I hope someone can help with a tricky formula. Given this type of
> > sample data:
>
> > A B C D E
> > F G
> > 1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10
> > 2 x
> > x x
> > 3 x x x x
> > 4 x x
> > 5
> > x x x
> > 6
> > 7
>
> > Looking at row 2 -
> > In cell A6, find the first (left most) x and return the month-year in
> > the row above it. This should give Apr-10.
> > In cell A7, find the last (right most) x and return the month-year in
> > the row above it. This should give Jun-10.
> > Then I should be able to determine the formula for rows 3 to 5.
>
> > If someone knows this, it will save a lot of people manual entries.
>
> > Thanks!
>
> > Michele

Thank you very much. Your formulas work great on a test worksheet.
Now I just have to determine why they are giving me a #NA - value not
available to the formula or function. There are month/years in all
the appropriate rows. I'm sure I'll work it out. Thanks again.


 | 
Pages: 1
Prev: Date and Time
Next: Totalling Subtotals Less Than 1