Moving values from one colum to another when there is spaces betwe

L

Lii Hadin

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
 
P

Pranav Vaidya

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

Lii Hadin

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
 
B

Bob Phillips

=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)
 
L

Lii Hadin

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
 
B

Bob Phillips

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

Lii Hadin

Where in the formula should I put instead of?

Bob Phillips said:
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)
Click to expand...
 
D

David Biddulph

Bob was suggesting that you might need semi-colons instead of commas, but
that will depend on your regional settings.
 
B

Bob Phillips

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

Lii Hadin

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
 
B

Bob Phillips

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

Bob Phillips

not received anything yet.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top