ExcelBanter - Combining formulas does not work

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Combining formulas does not work (https://www.excelbanter.com/excel-worksheet-functions/144197-combining-formulas-does-not-work.html)

Combining formulas does not work

I have the following model:

............A........B
1.........1........blank
2.........2........a
3.........3........a
4.........4........1
5.........5........2
6.........6........blank
7.........7........b
8.........8........blank
9.........9........1

In C1 I have:
=IF(COUNTIF($B$1:B1;B1)=1;A1;10)
which I copy down to C9

In D1 I have:
=SMALL($C$1:$C$9;A1)
which I copy down to D9

In this D column I get:
2
4
5
7
10
10
10
10
10
which is what I want.

However, combining the two formulas into one does not work. I've tried
in E1:
=SMALL(IF(COUNTIF($B$1:B1;B1)=1;A1;10):A1) which I copy down to E9

I get:
10
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!

Nothing to do with the desired result. I have no idea why it doesn't
work.

Any help?

Combining formulas does not work

Try this array formula**

A1:A9 = rng1
B1:B9 = rng2

=IF(ROWS($1:1)<=SUM((rng2<"")/COUNTIF(rng2,rng2&"")),INDEX(rng1,SMALL(IF(rng2<" ",IF(ROW(rng2)-MIN(ROW(rng2))+1=MATCH(rng2,rng2,0),ROW(rng2)-MIN(ROW(rng2))+1)),ROWS($1:1))),10)

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"vsoler" wrote in message
oups.com...
I have the following model:

...........A........B
1.........1........blank
2.........2........a
3.........3........a
4.........4........1
5.........5........2
6.........6........blank
7.........7........b
8.........8........blank
9.........9........1

In C1 I have:
=IF(COUNTIF($B$1:B1;B1)=1;A1;10)
which I copy down to C9

In D1 I have:
=SMALL($C$1:$C$9;A1)
which I copy down to D9

In this D column I get:
2
4
5
7
10
10
10
10
10
which is what I want.

However, combining the two formulas into one does not work. I've tried
in E1:
=SMALL(IF(COUNTIF($B$1:B1;B1)=1;A1;10):A1) which I copy down to E9

I get:
10
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!

Nothing to do with the desired result. I have no idea why it doesn't
work.

Any help?

Combining formulas does not work

On 26 mayo, 04:07, "T. Valko" wrote:
Try this array formula**

A1:A9 = rng1
B1:B9 = rng2

=IF(ROWS($1:1)<=SUM((rng2<"")/COUNTIF(rng2,rng2&"")),INDEX(rng1,SMALL(IF(r*ng2< "",IF(ROW(rng2)-MIN(ROW(rng2))+1=MATCH(rng2,rng2,0),ROW(rng2)-MIN(ROW(*rng2))+1)),ROWS($1:1))),10)

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"vsoler" wrote in message

oups.com...

I have the following model:

...........A........B
1.........1........blank
2.........2........a
3.........3........a
4.........4........1
5.........5........2
6.........6........blank
7.........7........b
8.........8........blank
9.........9........1

In C1 I have:
=IF(COUNTIF($B$1:B1;B1)=1;A1;10)
which I copy down to C9

In D1 I have:
=SMALL($C$1:$C$9;A1)
which I copy down to D9

In this D column I get:
2
4
5
7
10
10
10
10
10
which is what I want.

However, combining the two formulas into one does not work. I've tried
in E1:
=SMALL(IF(COUNTIF($B$1:B1;B1)=1;A1;10):A1) which I copy down to E9

I get:
10
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!

Nothing to do with the desired result. I have no idea why it doesn't
work.

Any help?- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko,

Your great. I wish I could write formulas like you do.

You always help a lot.

Thank you

Combining formulas does not work

You're welcome. Thanks for the feedback!

I wish I could write formulas like you do.

There's no reason why you can't. Spend a couple of hours a day in these
forums and study the replies and practice them using different scenarios.
Before you know it, you'll be writing formulas with the best of 'em (except
Harlan).

Biff

"vsoler" wrote in message
oups.com...
On 26 mayo, 04:07, "T. Valko" wrote:
Try this array formula**

A1:A9 = rng1
B1:B9 = rng2

=IF(ROWS($1:1)<=SUM((rng2<"")/COUNTIF(rng2,rng2&"")),INDEX(rng1,SMALL(IF(r*ng2< "",IF(ROW(rng2)-MIN(ROW(rng2))+1=MATCH(rng2,rng2,0),ROW(rng2)-MIN(ROW(*rng2))+1)),ROWS($1:1))),10)

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"vsoler" wrote in message

oups.com...

I have the following model:

...........A........B
1.........1........blank
2.........2........a
3.........3........a
4.........4........1
5.........5........2
6.........6........blank
7.........7........b
8.........8........blank
9.........9........1

In C1 I have:
=IF(COUNTIF($B$1:B1;B1)=1;A1;10)
which I copy down to C9

In D1 I have:
=SMALL($C$1:$C$9;A1)
which I copy down to D9

In this D column I get:
2
4
5
7
10
10
10
10
10
which is what I want.

However, combining the two formulas into one does not work. I've tried
in E1:
=SMALL(IF(COUNTIF($B$1:B1;B1)=1;A1;10):A1) which I copy down to E9

I get:
10
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!

Nothing to do with the desired result. I have no idea why it doesn't
work.

Any help?- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko,

Your great. I wish I could write formulas like you do.

You always help a lot.

Thank you