| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| repeat cell values into other cells x times | Excel Discussion (Misc queries) | |||
| Count the number of times a cell value is within a specific range | Excel Worksheet Functions | |||
| Using an Excel VB-made form in cell-specific conditions. | Excel Worksheet Functions | |||
| How to return the row # of an expression in specific array of cell | Excel Worksheet Functions | |||
| Counting the number of times a specific character appears in a cell | Excel Worksheet Functions | |||
Hybrid Mode
