| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving values from one colum to another when there is spaces betwe
Hi,
I have a real situation here. I have one colum with values on every forth row. Now, I want to make another colum where the values from the first column is added, when added in the first coulumn, WITHOUT the spaces. This is obviously impossible and after trying every function in the program I really need help here. Thanks a lot in advance! BR Lii Hadin |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving values from one colum to another when there is spaces betwe
I assume thisis your situation
Cell Value A1 1 A2 A3 A4 2 A5 A6 A7 3 A8 A9 A10 4 And you want the sum of only those cells where there is a number. Try the below formula, hope it works- =SUMIF(B1:B10,"<""",B1:B10) -- Pranav Vaidya VBA Developer PN, MH-India "Lii Hadin" wrote: Hi, I have a real situation here. I have one colum with values on every forth row. Now, I want to make another colum where the values from the first column is added, when added in the first coulumn, WITHOUT the spaces. This is obviously impossible and after trying every function in the program I really need help here. Thanks a lot in advance! BR Lii Hadin |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving values from one colum to another when there is spaces b
Hello, thanks for the suggestion but it did not work unfortunately. It is not
the sum I want to have. Like this: row value a1 a2 a3 a4 2 a5 a6 a7 a8 3 a9 a10 a11 a12 4 There are values in every forth row and I want organise them like this: a4 2 a8 3 a12 4 Every forth cell should be added to this new column and it should be automatically transfered if more values in the first coulum is added. Help me. BR Lii "Pranav Vaidya" wrote: I assume thisis your situation Cell Value A1 1 A2 A3 A4 2 A5 A6 A7 3 A8 A9 A10 4 And you want the sum of only those cells where there is a number. Try the below formula, hope it works- =SUMIF(B1:B10,"<""",B1:B10) -- Pranav Vaidya VBA Developer PN, MH-India "Lii Hadin" wrote: Hi, I have a real situation here. I have one colum with values on every forth row. Now, I want to make another colum where the values from the first column is added, when added in the first coulumn, WITHOUT the spaces. This is obviously impossible and after trying every function in the program I really need help here. Thanks a lot in advance! BR Lii Hadin |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving values from one colum to another when there is spaces b
=IF(ISERROR(SMALL(IF($A$1:$A$20<"",ROW($A$1:$A$20 ),""),ROW($A1))),"",
INDEX($A$1:$A$20,SMALL(IF($A$1:$A$20<"",ROW($A$1: $A$20),""),ROW($A1)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Then copy down as far as you think you will need. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Hello, thanks for the suggestion but it did not work unfortunately. It is not the sum I want to have. Like this: row value a1 a2 a3 a4 2 a5 a6 a7 a8 3 a9 a10 a11 a12 4 There are values in every forth row and I want organise them like this: a4 2 a8 3 a12 4 Every forth cell should be added to this new column and it should be automatically transfered if more values in the first coulum is added. Help me. BR Lii "Pranav Vaidya" wrote: I assume thisis your situation Cell Value A1 1 A2 A3 A4 2 A5 A6 A7 3 A8 A9 A10 4 And you want the sum of only those cells where there is a number. Try the below formula, hope it works- =SUMIF(B1:B10,"<""",B1:B10) -- Pranav Vaidya VBA Developer PN, MH-India "Lii Hadin" wrote: Hi, I have a real situation here. I have one colum with values on every forth row. Now, I want to make another colum where the values from the first column is added, when added in the first coulumn, WITHOUT the spaces. This is obviously impossible and after trying every function in the program I really need help here. Thanks a lot in advance! BR Lii Hadin |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving values from one colum to another when there is spaces b
So, I rewrote the formula in swedish ( I hope small means the smallest value)
and it looked like this: =om(ärfel(minsta(om('Inspection protocol'!$H$14:$H$19999<""),rad('Inspection protocol'!$H$14:$H$19999),""),rad('Inspection protocol'!$H14)),"",) Inspection protocol is just the other sheet from where I am taking the values. The formula does not get accepted though. Excel suggests that there is something wrong with "" after <. Suggestions? Lii "Bob Phillips" wrote: =IF(ISERROR(SMALL(IF($A$1:$A$20<"",ROW($A$1:$A$20 ),""),ROW($A1))),"", INDEX($A$1:$A$20,SMALL(IF($A$1:$A$20<"",ROW($A$1: $A$20),""),ROW($A1)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Then copy down as far as you think you will need. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Hello, thanks for the suggestion but it did not work unfortunately. It is not the sum I want to have. Like this: row value a1 a2 a3 a4 2 a5 a6 a7 a8 3 a9 a10 a11 a12 4 There are values in every forth row and I want organise them like this: a4 2 a8 3 a12 4 Every forth cell should be added to this new column and it should be automatically transfered if more values in the first coulum is added. Help me. BR Lii "Pranav Vaidya" wrote: I assume thisis your situation Cell Value A1 1 A2 A3 A4 2 A5 A6 A7 3 A8 A9 A10 4 And you want the sum of only those cells where there is a number. Try the below formula, hope it works- =SUMIF(B1:B10,"<""",B1:B10) -- Pranav Vaidya VBA Developer PN, MH-India "Lii Hadin" wrote: Hi, I have a real situation here. I have one colum with values on every forth row. Now, I want to make another colum where the values from the first column is added, when added in the first coulumn, WITHOUT the spaces. This is obviously impossible and after trying every function in the program I really need help here. Thanks a lot in advance! BR Lii Hadin |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving values from one colum to another when there is spaces b
Perhaps you should use ; instead of , as well. Don't forget to array enter
it. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... So, I rewrote the formula in swedish ( I hope small means the smallest value) and it looked like this: =om(ärfel(minsta(om('Inspection protocol'!$H$14:$H$19999<""),rad('Inspection protocol'!$H$14:$H$19999),""),rad('Inspection protocol'!$H14)),"",) Inspection protocol is just the other sheet from where I am taking the values. The formula does not get accepted though. Excel suggests that there is something wrong with "" after <. Suggestions? Lii "Bob Phillips" wrote: =IF(ISERROR(SMALL(IF($A$1:$A$20<"",ROW($A$1:$A$20 ),""),ROW($A1))),"", INDEX($A$1:$A$20,SMALL(IF($A$1:$A$20<"",ROW($A$1: $A$20),""),ROW($A1)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Then copy down as far as you think you will need. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Hello, thanks for the suggestion but it did not work unfortunately. It is not the sum I want to have. Like this: row value a1 a2 a3 a4 2 a5 a6 a7 a8 3 a9 a10 a11 a12 4 There are values in every forth row and I want organise them like this: a4 2 a8 3 a12 4 Every forth cell should be added to this new column and it should be automatically transfered if more values in the first coulum is added. Help me. BR Lii "Pranav Vaidya" wrote: I assume thisis your situation Cell Value A1 1 A2 A3 A4 2 A5 A6 A7 3 A8 A9 A10 4 And you want the sum of only those cells where there is a number. Try the below formula, hope it works- =SUMIF(B1:B10,"<""",B1:B10) -- Pranav Vaidya VBA Developer PN, MH-India "Lii Hadin" wrote: Hi, I have a real situation here. I have one colum with values on every forth row. Now, I want to make another colum where the values from the first column is added, when added in the first coulumn, WITHOUT the spaces. This is obviously impossible and after trying every function in the program I really need help here. Thanks a lot in advance! BR Lii Hadin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I add all values in a given row (not colum)? | Excel Worksheet Functions | |||
| Skipping a cell and moving on to the next, with no blanks in betwe | Excel Discussion (Misc queries) | |||
| add a colum within 5 spaces of a column | Excel Discussion (Misc queries) | |||
| colum B=average of last 10 values in A | Excel Worksheet Functions | |||
| lookup in colum a and compare values in colum b | Excel Worksheet Functions | |||
Hybrid Mode
