|
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 |
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 |
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 |
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 |
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 |
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 |
Moving values from one colum to another when there is spaces b
Where in the formula should I put instead of?
"Bob Phillips" wrote: 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 |
Moving values from one colum to another when there is spaces b
Bob was suggesting that you might need semi-colons instead of commas, but
that will depend on your regional settings. -- David Biddulph "Lii Hadin" wrote in message ... Where in the formula should I put instead of? "Bob Phillips" wrote: Perhaps you should use ; instead of , as well. Don't forget to array enter it. "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 |
Moving values from one colum to another when there is spaces b
It should be either
=OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))),"", INDEX('Inspection protocol'!$H$14:$H$19999,MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))-RAD($H$14)+1)) or =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1)));""; INDEX('Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1))-RAD($H$14)+1)) don't forget to array-enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Where in the formula should I put instead of? "Bob Phillips" wrote: 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 |
Moving values from one colum to another when there is spaces b
I understand. Tried again today and changed the formula with: A1 to A5 (start
new column i row 5 instead of 1) and put Inspection protocol before the last reference to H14. It lookes like this: =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5)));"";INDEX( 'Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5))-RAD('Inspection protocol'!$H$14)+1)) Now when I array-enter nothing is happening. No error warnings or anything, and no figure in the cell. Tried to copy and array-enter the formula to more rows but still no result. But hey, one step closer, at least there is no warning displayed. Lii "Bob Phillips" wrote: It should be either =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))),"", INDEX('Inspection protocol'!$H$14:$H$19999,MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))-RAD($H$14)+1)) or =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1)));""; INDEX('Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1))-RAD($H$14)+1)) don't forget to array-enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Where in the formula should I put instead of? "Bob Phillips" wrote: 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 |
Moving values from one colum to another when there is spaces b
Lii,
send me your workbook and I will see what is happening. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... I understand. Tried again today and changed the formula with: A1 to A5 (start new column i row 5 instead of 1) and put Inspection protocol before the last reference to H14. It lookes like this: =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5)));"";INDEX( 'Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5))-RAD('Inspection protocol'!$H$14)+1)) Now when I array-enter nothing is happening. No error warnings or anything, and no figure in the cell. Tried to copy and array-enter the formula to more rows but still no result. But hey, one step closer, at least there is no warning displayed. Lii "Bob Phillips" wrote: It should be either =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))),"", INDEX('Inspection protocol'!$H$14:$H$19999,MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))-RAD($H$14)+1)) or =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1)));""; INDEX('Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1))-RAD($H$14)+1)) don't forget to array-enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Where in the formula should I put instead of? "Bob Phillips" wrote: 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 |
Moving values from one colum to another when there is spaces b
I sent it yo your inbox according to your mail stated under author profile.
BR Lii "Bob Phillips" wrote: Lii, send me your workbook and I will see what is happening. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... I understand. Tried again today and changed the formula with: A1 to A5 (start new column i row 5 instead of 1) and put Inspection protocol before the last reference to H14. It lookes like this: =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5)));"";INDEX( 'Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5))-RAD('Inspection protocol'!$H$14)+1)) Now when I array-enter nothing is happening. No error warnings or anything, and no figure in the cell. Tried to copy and array-enter the formula to more rows but still no result. But hey, one step closer, at least there is no warning displayed. Lii "Bob Phillips" wrote: It should be either =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))),"", INDEX('Inspection protocol'!$H$14:$H$19999,MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))-RAD($H$14)+1)) or =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1)));""; INDEX('Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1))-RAD($H$14)+1)) don't forget to array-enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Where in the formula should I put instead of? "Bob Phillips" wrote: 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 |
Moving values from one colum to another when there is spaces b
not received anything yet.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... I sent it yo your inbox according to your mail stated under author profile. BR Lii "Bob Phillips" wrote: Lii, send me your workbook and I will see what is happening. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... I understand. Tried again today and changed the formula with: A1 to A5 (start new column i row 5 instead of 1) and put Inspection protocol before the last reference to H14. It lookes like this: =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5)));"";INDEX( 'Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5))-RAD('Inspection protocol'!$H$14)+1)) Now when I array-enter nothing is happening. No error warnings or anything, and no figure in the cell. Tried to copy and array-enter the formula to more rows but still no result. But hey, one step closer, at least there is no warning displayed. Lii "Bob Phillips" wrote: It should be either =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))),"", INDEX('Inspection protocol'!$H$14:$H$19999,MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))-RAD($H$14)+1)) or =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1)));""; INDEX('Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1))-RAD($H$14)+1)) don't forget to array-enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Where in the formula should I put instead of? "Bob Phillips" wrote: 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 |
Moving values from one colum to another when there is spaces b
I'll try again
"Bob Phillips" wrote: not received anything yet. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... I sent it yo your inbox according to your mail stated under author profile. BR Lii "Bob Phillips" wrote: Lii, send me your workbook and I will see what is happening. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... I understand. Tried again today and changed the formula with: A1 to A5 (start new column i row 5 instead of 1) and put Inspection protocol before the last reference to H14. It lookes like this: =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5)));"";INDEX( 'Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A5))-RAD('Inspection protocol'!$H$14)+1)) Now when I array-enter nothing is happening. No error warnings or anything, and no figure in the cell. Tried to copy and array-enter the formula to more rows but still no result. But hey, one step closer, at least there is no warning displayed. Lii "Bob Phillips" wrote: It should be either =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))),"", INDEX('Inspection protocol'!$H$14:$H$19999,MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"",RAD('Inspection protocol'!$H$14:$H$19999),""),RAD($A1))-RAD($H$14)+1)) or =OM(ÄRFEL(MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1)));""; INDEX('Inspection protocol'!$H$14:$H$19999;MINSTA(OM('Inspection protocol'!$H$14:$H$19999<"";RAD('Inspection protocol'!$H$14:$H$19999);"");RAD($A1))-RAD($H$14)+1)) don't forget to array-enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lii Hadin" wrote in message ... Where in the formula should I put instead of? "Bob Phillips" wrote: 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 |
Moving values from one colum to another when there is spaces b
|
| All times are GMT +1. The time now is 02:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com