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

L

liups

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.
 
T

T. Valko

Try this:

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

Biff
 
R

Roger Govier

Hi Biff

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

T. Valko

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
 
R

Roger Govier

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.
 
L

liups

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!
 
R

Roger Govier

Hi

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

Gives result of £180.30
 
R

Roger Govier

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.
 
B

Bernd

Hello,

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

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

Regards,
Bernd
 
B

Bob Phillips

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)
 
T

T. Valko

="{"&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
 
T

T. Valko

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
 
R

Roger Govier

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.
 
T

T. Valko

I guess we just have a different interpretation of what the OP originally
asked for.
The OP's original request was for {100, 100, 100}
without any trailing commas or spaces.
Your formula did leave trailing comma and space.
Click to expand...

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
 
R

Roger Govier

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.
 
B

Bob Phillips

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

T. Valko

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

We can blame the OP! <VVBG>

Biff
 
Top