min and if again

[jam]

Hi
I posted this message, I got the next answer, I copied the array
formulae and paste it , it gives error, I enter it by typing still give
error
Any help?
Jam
Hi
Anyone can solve this problem
I have 2 rows
a b c d e f
46 55 66 46 55 56
I used min fuction it gives me the first 46
I want to to hvae the 2 46 with the names a and d
If can not solve this since it has one criterion
Thanks for any help
Jam

Hi!
Assume this data is in the range A1:F2
a b c d e f
46 55 66 46 55 56
Enter this formulas as an array using the key combination of
CTRL,SHIFT,ENTER in cell A5 and copy across to F5:
=3DIF(COLUMNS($A:A)<=3DCOUNTIF($A2:$F2,MIN($A2:$F2)),INDEX($A1:$F1,SMALL(IF=
($A2=AD:$F2=3DMIN($A2:$F2),COLUMN($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A)))=
,"")

Enter this formula in A6 and copy across to F6:
=3DIF(A5=3D"","",INDEX($A2:$F2,MATCH(A5,$A1:$F1,0)))
The results will be:
..=2E........A..........B..........C..........D..........E..........F.......=
..=2E=AD.

5=2E.......a...........d...................................................=
..=2E=AD.......

6=2E......46.........46....................................................=
..=2E=AD.....



Biff

[Bob Phillips]

Works fine for me, did you array-enter it? That is commit with
Ctrl-Shift-Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"jam" <[email protected]> wrote in message
news:[email protected]...
Hi
I posted this message, I got the next answer, I copied the array
formulae and paste it , it gives error, I enter it by typing still give
error
Any help?
Jam
Hi
Anyone can solve this problem
I have 2 rows
a b c d e f
46 55 66 46 55 56
I used min fuction it gives me the first 46
I want to to hvae the 2 46 with the names a and d
If can not solve this since it has one criterion
Thanks for any help
Jam

Hi!
Assume this data is in the range A1:F2
a b c d e f
46 55 66 46 55 56
Enter this formulas as an array using the key combination of
CTRL,SHIFT,ENTER in cell A5 and copy across to F5:
=IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),INDEX($A1:$F1,SMALL(IF($A2*
:$F2=MIN($A2:$F2),COLUMN($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),"")

Enter this formula in A6 and copy across to F6:
=IF(A5="","",INDEX($A2:$F2,MATCH(A5,$A1:$F1,0)))
The results will be:
...........A..........B..........C..........D..........E..........F.........*
..

5........a...........d.....................................................*
........

6.......46.........46......................................................*
......



Biff

[Biff]

Hi!

>I copied the array formulae and paste it , it gives error

What kind of error?

Copying and pasting an array formula will not make it an array formula. You
MUST use the specific key combination to make it an array formula. Select
the first cell where this formula is entered. Press function key F2. This
will put you in EDIT mode. Now, hold down both the CTRL key and the SHIFT
key. While holding down both of those keys hit the ENTER key. If done
properly Excel will enclose the formula (look at it in the formula bar) in
squiggly braces { }. You cannot just type these braces in. You MUST use the
key combination to produce them. Also, any time you EDIT an array formula
(like you just did), the formula MUST be re-entered as an array.

If you're still having problems post the *EXACT* formula you are using.

Biff

"jam" <[email protected]> wrote in message
news:[email protected]...
Hi
I posted this message, I got the next answer, I copied the array
formulae and paste it , it gives error, I enter it by typing still give
error
Any help?
Jam
Hi
Anyone can solve this problem
I have 2 rows
a b c d e f
46 55 66 46 55 56
I used min fuction it gives me the first 46
I want to to hvae the 2 46 with the names a and d
If can not solve this since it has one criterion
Thanks for any help
Jam

Hi!
Assume this data is in the range A1:F2
a b c d e f
46 55 66 46 55 56
Enter this formulas as an array using the key combination of
CTRL,SHIFT,ENTER in cell A5 and copy across to F5:
=IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),INDEX($A1:$F1,SMALL(IF($A2*:$F2=MIN($A2:$F2),COLUMN($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),"")

Enter this formula in A6 and copy across to F6:
=IF(A5="","",INDEX($A2:$F2,MATCH(A5,$A1:$F1,0)))
The results will be:
...........A..........B..........C..........D..........E..........F.........*.

5........a...........d.....................................................*.......

6.......46.........46......................................................*.....



Biff

[jam]

Hi
Well I swear that I entered the formulae, and in edit mode, I hold down
the ctrt. shift, and enter keys, it hilights the columns and rows, but
it does not put the brackets{}, and then I get an error message
The formula is
=3DIF(COLUMNS($A:A)<=3DCOUNTIF($A2:$F2,MIN($A2:$F2)),INDEX($A1:$F1,SMALL(IF=
($A2=AD=AD:$F2=3DMIN($A2:$F2),COLUMN($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A=
))),""),
the same you posted me, still give error, I have the impression that
the array formulae entering is not working, any help? I am using excel
2003
Jam

[Biff]

Ok, I think I know what the problem is.........Google Groups!

It looks like you're posting through Google Groups.

Ever since they changed to this new version there have been problems
reported that when messages are posted some "junk" gets inserted into the
message. "Junk" refers to characters that were not typed by the poster.

See this screencap:

http://img518.imageshack.us/img518/8...problem6qw.jpg

These 2 formulas were copied from both of your posts in this thread and then
pasted into a worksheet. Notice the highlighted sections. After $A2 you see
those dashes (-), well, those aren't supposed to be there and that is some
of the "junk" that I was referring to. I use OE to view this group and I
don't see those dashes in any of the posts but they are there as evidenced
by them appearing when the formulas are pasted into a worksheet.

So, remove all the "junk", re-enter the fomula as an array and it should
work.

Biff

"jam" <[email protected]> wrote in message
news:[email protected]...
Hi
Well I swear that I entered the formulae, and in edit mode, I hold down
the ctrt. shift, and enter keys, it hilights the columns and rows, but
it does not put the brackets{}, and then I get an error message
The formula is
=IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),INDEX($A1:$F1,SMALL(IF($A2**:$F2=MIN($A2:$F2),COLUMN($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),""),
the same you posted me, still give error, I have the impression that
the array formulae entering is not working, any help? I am using excel
2003
Jam

[Biff]

Ok, my suspicions are confirmed.

I just viewed this thread in Google Groups and sure enough, those dashes
show up there.

I did not type in those dashes. They are not part of the formula. They're
Google "junk"!

Biff

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Ok, I think I know what the problem is.........Google Groups!
>
> It looks like you're posting through Google Groups.
>
> Ever since they changed to this new version there have been problems
> reported that when messages are posted some "junk" gets inserted into the
> message. "Junk" refers to characters that were not typed by the poster.
>
> See this screencap:
>
> http://img518.imageshack.us/img518/8...problem6qw.jpg
>
> These 2 formulas were copied from both of your posts in this thread and
> then pasted into a worksheet. Notice the highlighted sections. After $A2
> you see those dashes (-), well, those aren't supposed to be there and that
> is some of the "junk" that I was referring to. I use OE to view this group
> and I don't see those dashes in any of the posts but they are there as
> evidenced by them appearing when the formulas are pasted into a worksheet.
>
> So, remove all the "junk", re-enter the fomula as an array and it should
> work.
>
> Biff
>
> "jam" <[email protected]> wrote in message
> news:[email protected]...
> Hi
> Well I swear that I entered the formulae, and in edit mode, I hold down
> the ctrt. shift, and enter keys, it hilights the columns and rows, but
> it does not put the brackets{}, and then I get an error message
> The formula is
> =IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),INDEX($A1:$F1,SMALL(IF($A2**:$F2=MIN($A2:$F2),COLUMN($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),""),
> the same you posted me, still give error, I have the impression that
> the array formulae entering is not working, any help? I am using excel
> 2003
> Jam
>
>

[jam]

Hi Biff
Thanks for your help, I think that there is not only junk things, but
that eats the brackets
I've counted the opened and closed brackets that doea not match, tried
to see where, could not, any hel?
Thanks
Jam
Biff wrote:
> Ok, my suspicions are confirmed.
>
> I just viewed this thread in Google Groups and sure enough, those dashes
> show up there.
>
> I did not type in those dashes. They are not part of the formula. They're
> Google "junk"!
>
> Biff
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
> > Ok, I think I know what the problem is.........Google Groups!
> >
> > It looks like you're posting through Google Groups.
> >
> > Ever since they changed to this new version there have been problems
> > reported that when messages are posted some "junk" gets inserted into t=
he
> > message. "Junk" refers to characters that were not typed by the poster.
> >
> > See this screencap:
> >
> > http://img518.imageshack.us/img518/8...problem6qw.jpg
> >
> > These 2 formulas were copied from both of your posts in this thread and
> > then pasted into a worksheet. Notice the highlighted sections. After $A2
> > you see those dashes (-), well, those aren't supposed to be there and t=
hat
> > is some of the "junk" that I was referring to. I use OE to view this gr=
oup
> > and I don't see those dashes in any of the posts but they are there as
> > evidenced by them appearing when the formulas are pasted into a workshe=
et.
> >
> > So, remove all the "junk", re-enter the fomula as an array and it should
> > work.
> >
> > Biff
> >
> > "jam" <[email protected]> wrote in message
> > news:[email protected]...
> > Hi
> > Well I swear that I entered the formulae, and in edit mode, I hold down
> > the ctrt. shift, and enter keys, it hilights the columns and rows, but
> > it does not put the brackets{}, and then I get an error message
> > The formula is
> > =3DIF(COLUMNS($A:A)<=3DCOUNTIF($A2:$F2,MIN($A2:$F2)),INDEX($A1:$F1,SMAL=
L(IF($A2=AD=AD:$F2=3DMIN($A2:$F2),COLUMN($A1:$F1)-COLUMN($A:$A)+1),COLUMNS(=
$A:A))),""),
> > the same you posted me, still give error, I have the impression that
> > the array formulae entering is not working, any help? I am using excel
> > 2003
> > Jam
> >
> >