How to repeat one cell specific times to form an array?
 

Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula, then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I
don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

thank you guys, that worked but that's different with what I'm
thinking, I'm trying to get a real array, yes which can be used in
another formula, like in
=NPV(0.04, -90, xxxxx(a1,b1))
by xxxx(a1,b1) I hope I can get an array which is a1 repeated b1
times, the result will be
=NPV(0.04, -90, {100,100,100})

can I do that without using VBA?

thanks a lot!

On May 13, 2:53 pm, "T. Valko" wrote:
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message

...

Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier

"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
roups.com...
Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Hi

With
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
in cell C1
InsertNameDefine Name params Formula =EVALUATE(C1)
then use
=NPV(0.04, -90,params)

Gives result of £180.30

--
Regards

Roger Govier


"liups" wrote in message
ps.com...
thank you guys, that worked but that's different with what I'm
thinking, I'm trying to get a real array, yes which can be used in
another formula, like in
=NPV(0.04, -90, xxxxx(a1,b1))
by xxxx(a1,b1) I hope I can get an array which is a1 repeated b1
times, the result will be
=NPV(0.04, -90, {100,100,100})

can I do that without using VBA?

thanks a lot!

On May 13, 2:53 pm, "T. Valko" wrote:
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message

...

Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier

"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
roups.com...
Hi,
I need to repeat the content of a cell to form an array, like
this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I
don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Hi

Better make that Inserted formula
=EVALUATE($C$1)
unless of course you wanted to have different values in A2, B2 and copy
the formula in C1 down to C2.
You could then leave the row relative and use $C1, and as you copy your
NPV formula down the page it would adjust to different values for the
params.

--
Regards

Roger Govier


"liups" wrote in message
ps.com...
thank you guys, that worked but that's different with what I'm
thinking, I'm trying to get a real array, yes which can be used in
another formula, like in
=NPV(0.04, -90, xxxxx(a1,b1))
by xxxx(a1,b1) I hope I can get an array which is a1 repeated b1
times, the result will be
=NPV(0.04, -90, {100,100,100})

can I do that without using VBA?

thanks a lot!

On May 13, 2:53 pm, "T. Valko" wrote:
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message

...

Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier

"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
roups.com...
Hi,
I need to repeat the content of a cell to form an array, like
this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I
don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Hello,

=a1*row(indirect("1:"&b1))/row(indirect("1:"&b1))

Wrap this into a TRANSPOSE() if necessary...

Regards,
Bernd

How to repeat one cell specific times to form an array?
 

="{"&SUBSTITUTE(REPT(A1&", ",B1),",","",B1)&"}"

"liups" wrote:

Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Note that Biff's formula uses ", " throughout Roger, with a comma space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Roger Govier" wrote in message
...
Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula, then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

="{"&SUBSTITUTE(REPT(A1&", ",B1),",","",B1)&"}"

That still leaves a space after the last number:

{100, 100, 100 }

="{"&TRIM(SUBSTITUTE(REPT(A1&", ",B1),",","",B1))&"}"

{100, 100, 100}

Biff

"Teethless mama" wrote in message
...
="{"&SUBSTITUTE(REPT(A1&", ",B1),",","",B1)&"}"

"liups" wrote:

Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Yes, Bob, but the space is actually superfluous.
I would change the ", " to ","

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Note that Biff's formula uses ", " throughout Roger, with a comma
space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Roger Govier" wrote in message
...
Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula,
then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like
this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I
don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

the space is actually superfluous.

Yes, in the final analysis after we found out what the OP was really up to.
But, it answered the original question as posed.

Biff

"Roger Govier" wrote in message
...
Yes, Bob, but the space is actually superfluous.
I would change the ", " to ","

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Note that Biff's formula uses ", " throughout Roger, with a comma space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Roger Govier" wrote in message
...
Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula, then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Hi Biff

I wasn't trying to be derogatory about your use of the space, merely
pointing out that it wasn't necessary.
The OP's original request was for {100, 100, 100}
without any trailing commas or spaces.
Your formula did leave trailing comma and space.

As you rightly guessed, they wanted to use it as an array in another
formula, which works equally well, with or without spaces.

Certainly no offence intended on my part.
--
Regards

Roger Govier


"T. Valko" wrote in message
...
the space is actually superfluous.

Yes, in the final analysis after we found out what the OP was really
up to. But, it answered the original question as posed.

Biff

"Roger Govier" wrote in message
...
Yes, Bob, but the space is actually superfluous.
I would change the ", " to ","

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Note that Biff's formula uses ", " throughout Roger, with a comma
space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)

"Roger Govier" wrote in message
...
Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula,
then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like
this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I
don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

I guess we just have a different interpretation of what the OP originally
asked for.

I want to put in C1 an array like this :{100, 100, 100}

The OP's original request was for {100, 100, 100}
without any trailing commas or spaces.
Your formula did leave trailing comma and space.

That's *exactly* what the OP asked for: {100, 100, 100}

I see a trailing comma and space after each element except the last. So the
formula I suggested did just that. Unless the request is for something that
is obviously incorrect or terribly inefficient I try to give 'em exactly
what they ask for. If that turns out to be inappropriate or they discover
that it didn't do what they had expected then that usually will generate
follow-ups in which we can refine things. This thread is a perfect example
of that!

Biff

"Roger Govier" wrote in message
...
Hi Biff

I wasn't trying to be derogatory about your use of the space, merely
pointing out that it wasn't necessary.
The OP's original request was for {100, 100, 100}
without any trailing commas or spaces.
Your formula did leave trailing comma and space.

As you rightly guessed, they wanted to use it as an array in another
formula, which works equally well, with or without spaces.

Certainly no offence intended on my part.
--
Regards

Roger Govier


"T. Valko" wrote in message
...
the space is actually superfluous.

Yes, in the final analysis after we found out what the OP was really up
to. But, it answered the original question as posed.

Biff

"Roger Govier" wrote in message
...
Yes, Bob, but the space is actually superfluous.
I would change the ", " to ","

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Note that Biff's formula uses ", " throughout Roger, with a comma
space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Roger Govier" wrote in message
...
Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula, then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like
this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I
don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

Hi Biff
I think we are getting ourselves all tied up in a knot here, all through
a mistake on my part and I fully apologise.

I can now see that formula wrapping, when I pasted your solution into my
spreadsheet, caused your formula to retain the trailing comma and space,
and I can also *now* see that was what Bob was pointing out in his post.

On my machine, with both your original formula and mine, I get {100,
100, 100, }
So, your formula did not *appear* to be "cleaning up". I posted an
amendment to mine
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
which does produce what the OP requested {100, 100, 100} (and is still
somewhat shorter<bg)

I had mistakenly thought that Bob was suggesting my formula needed a
trailing space (sorry Bob), and posted (with knowledge of the OP's
subsequent posting) that spaces were superfluous.

My final offering to the OP still did retain the first 2 spaces, but for
the NPV formula it makes no difference to the array whether they are
included or not.

Once again, apologies for the confusion caused.
--
Regards

Roger Govier


"T. Valko" wrote in message
...
I guess we just have a different interpretation of what the OP
originally asked for.

I want to put in C1 an array like this :{100, 100, 100}

The OP's original request was for {100, 100, 100}
without any trailing commas or spaces.
Your formula did leave trailing comma and space.

That's *exactly* what the OP asked for: {100, 100, 100}

I see a trailing comma and space after each element except the last.
So the formula I suggested did just that. Unless the request is for
something that is obviously incorrect or terribly inefficient I try to
give 'em exactly what they ask for. If that turns out to be
inappropriate or they discover that it didn't do what they had
expected then that usually will generate follow-ups in which we can
refine things. This thread is a perfect example of that!

Biff

"Roger Govier" wrote in message
...
Hi Biff

I wasn't trying to be derogatory about your use of the space, merely
pointing out that it wasn't necessary.
The OP's original request was for {100, 100, 100}
without any trailing commas or spaces.
Your formula did leave trailing comma and space.

As you rightly guessed, they wanted to use it as an array in another
formula, which works equally well, with or without spaces.

Certainly no offence intended on my part.
--
Regards

Roger Govier


"T. Valko" wrote in message
...
the space is actually superfluous.

Yes, in the final analysis after we found out what the OP was really
up to. But, it answered the original question as posed.

Biff

"Roger Govier" wrote in message
...
Yes, Bob, but the space is actually superfluous.
I would change the ", " to ","

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Note that Biff's formula uses ", " throughout Roger, with a comma
space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)

"Roger Govier" wrote in message
...
Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula,
then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another
formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&",
",B1),LEN(REPT(A1&", ",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like
this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but
I don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

I was only actually pointing out how to get a different result with the
formula given, not commenting on the solutions, as Roger seemed to think
they were the same.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"T. Valko" wrote in message
...
the space is actually superfluous.

Yes, in the final analysis after we found out what the OP was really up
to. But, it answered the original question as posed.

Biff

"Roger Govier" wrote in message
...
Yes, Bob, but the space is actually superfluous.
I would change the ", " to ","

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Note that Biff's formula uses ", " throughout Roger, with a comma space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Roger Govier" wrote in message
...
Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula, then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I
don't
know how to write the formula, any ideas?

thank you very much.

How to repeat one cell specific times to form an array?
 

I think we are getting ourselves all tied up in a knot here

We can blame the OP! <VVBG

Biff

"Roger Govier" wrote in message
...
Hi Biff
I think we are getting ourselves all tied up in a knot here, all through a
mistake on my part and I fully apologise.

I can now see that formula wrapping, when I pasted your solution into my
spreadsheet, caused your formula to retain the trailing comma and space,
and I can also *now* see that was what Bob was pointing out in his post.

On my machine, with both your original formula and mine, I get {100, 100,
100, }
So, your formula did not *appear* to be "cleaning up". I posted an
amendment to mine
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
which does produce what the OP requested {100, 100, 100} (and is still
somewhat shorter<bg)

I had mistakenly thought that Bob was suggesting my formula needed a
trailing space (sorry Bob), and posted (with knowledge of the OP's
subsequent posting) that spaces were superfluous.

My final offering to the OP still did retain the first 2 spaces, but for
the NPV formula it makes no difference to the array whether they are
included or not.

Once again, apologies for the confusion caused.
--
Regards

Roger Govier


"T. Valko" wrote in message
...
I guess we just have a different interpretation of what the OP originally
asked for.

I want to put in C1 an array like this :{100, 100, 100}

The OP's original request was for {100, 100, 100}
without any trailing commas or spaces.
Your formula did leave trailing comma and space.

That's *exactly* what the OP asked for: {100, 100, 100}

I see a trailing comma and space after each element except the last. So
the formula I suggested did just that. Unless the request is for
something that is obviously incorrect or terribly inefficient I try to
give 'em exactly what they ask for. If that turns out to be inappropriate
or they discover that it didn't do what they had expected then that
usually will generate follow-ups in which we can refine things. This
thread is a perfect example of that!

Biff

"Roger Govier" wrote in message
...
Hi Biff

I wasn't trying to be derogatory about your use of the space, merely
pointing out that it wasn't necessary.
The OP's original request was for {100, 100, 100}
without any trailing commas or spaces.
Your formula did leave trailing comma and space.

As you rightly guessed, they wanted to use it as an array in another
formula, which works equally well, with or without spaces.

Certainly no offence intended on my part.
--
Regards

Roger Govier


"T. Valko" wrote in message
...
the space is actually superfluous.

Yes, in the final analysis after we found out what the OP was really up
to. But, it answered the original question as posed.

Biff

"Roger Govier" wrote in message
...
Yes, Bob, but the space is actually superfluous.
I would change the ", " to ","

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Note that Biff's formula uses ", " throughout Roger, with a comma
space.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Roger Govier" wrote in message
...
Hi Biff
I get {100, 100, 100, } with both formulae

If, as you say the OP wants to use it as part of another formula,
then
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1-1)&A1&"}")
seems to sort it out.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
That last part:

LEN(REPT(A1&", ",B1))-2)

Is being used to "clean-up". Without it, you get:

{100, 100, 100, }

With it, you get:

{100, 100, 100}

I'm wondering if the OP is trying to use this in another formula?

Biff

"Roger Govier" wrote in message
...
Hi Biff

Couldn't that be shortened to
=IF(COUNTA(A1:B1)<2,"","{"&REPT(A1&", ",B1)&"}")

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Try this:

=IF(COUNTA(A1:B1)<2,"","{"&LEFT(REPT(A1&", ",B1),LEN(REPT(A1&",
",B1))-2)&"}")

Biff

"liups" wrote in message
ups.com...
Hi,
I need to repeat the content of a cell to form an array, like
this:
A1: 100
B1: 3
I want to put in C1 an array like this :{100, 100, 100}, but I
don't
know how to write the formula, any ideas?

thank you very much.