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.