Why does the the following Formula Work if the Result is Displayed in Next To Adjacent Column ?

[RapchikM]

Hello

Why does the the following Formula Work if the Result is Displayed in Next To Adjacent Column ?

Formula written in E5, F5, G5, H5 for the Value in D5
=IFERROR(MID($D5,COLUMN()-(COLUMN($D5)-0),1)+0,0)

D5 VALUE = 7691
E5 = 7
F5 = 6
G5 = 9
H5 = 1

If i shift the above formula with respective column refernce nos to E10, F10, G10, H10
the value in each above reference column/cells displays 0 or Zero
For eg Formula Copied from E10 to H10
=IFERROR(MID($D5,COLUMN()-(COLUMN($D5)-0),1)+0,0)

Why ?

Any Wrong thing or wrong reference am I incorporating

Your input shall be appreciated

Thanks
RapchikM

 

[etaf]

column() will change
so
=IFERROR(MID($D5,COLUMN()-(COLUMN($D5)-0),1)+0,0)
==IFERROR(MID($D5,5-4-0),1)+0,0)
then when you move the formula the column() becomes 5,6,7,8 etc
=IFERROR(MID($D5,6-4-0),1)+0,0) (MID($D5,2,1)
=IFERROR(MID($D5,7-4-0),1)+0,0) (MID($D5,3,1)
etc

Book1
	D	E	F	G	H	I
5	7691	7	6	9	1	0
6	column($D5) =	4
7	column() =	5	6	7	8	9
Sheet1

Cell Formulas
Range	Formula
E5:I5	F5	=IFERROR(MID($D5,COLUMN()-(COLUMN($D5)-0),1)+0,0)
E6	E6	=COLUMN(D5)
E7:I7	E7	=COLUMN()

 

[RapchikM]

then when you move the formula the column() becomes 5,6,7,8 etc
=IFERROR(MID($D5,6-4-0),1)+0,0) (MID($D5,2,1)
=IFERROR(MID($D5,7-4-0),1)+0,0) (MID($D5,3,1)
etc

I get Error "You've entered too few arguements for this function

RapchikM

 

[etaf]

i was just showing the calculation when it converted column() so you could see the effect the column() was having - not a function that would work
its just an explanation

 

[RapchikM]

Although you have explained
I thought and wrote the formula as per your #2 ie why it showed error

=IFERROR(MID($D5,6-4-0),1)+0,0) (MID($D5,2,1)

Do i need to change the following
=IFERROR(MID($D5,COLUMN()-(COLUMN($D5)-0),1)+0,0)
To
=IFERROR(MID($D5,COLUMN(6-4-0)-(COLUMN($D5)-0),1)+0,0)
or how is it like

I've shifted the the cells from E5:H5 to E10:H10 with the original formula

So to use the same formula in new Cells From E10:H10 What needs to be corrected Dear ?

RapchikM

 

[etaf]

no, i was just explaining why it didn't work

Why ?

Any Wrong thing or wrong reference am I incorporating

so the results you get are correct

For eg Formula Copied from E10 to H10
then the formula will change to
=IFERROR(MID($D10,COLUMN()-(COLUMN($D10)-0),1)+0,0)
working on row 10
if you still want it to work with contents of D5

=IFERROR(MID($D$5,COLUMN()-(COLUMN($D10)-0),1)+0,0)

otherwise you will need to add content into D10

Book1
	D	E	F	G	H
1
2
3
4
5	7691	7	6	9	1
6	column($D5) =	4
7	column() =	5	6	7	8
8
9
10		7	6	9	1
Sheet1

Cell Formulas
Range	Formula
E5:H5	F5	=IFERROR(MID($D5,COLUMN()-(COLUMN($D5)-0),1)+0,0)
E6	E6	=COLUMN(D5)
E7:H7	E7	=COLUMN()
E10:H10	E10	=IFERROR(MID($D5,COLUMN()-(COLUMN($D10)-0),1)+0,0)

 

[RapchikM]

Etaf

=IFERROR(MID($D$5,COLUMN()-(COLUMN($D10)-0),1)+0,0)

otherwise you will need to add content into D10

I tried the above with Values in D5 = 7691
Range E10:H10 Displays 0

That means we are compelled to write the formula in to the next Adjacent Cells
ie if value is in D5 then formula to be written from E5:H5

Why to add Content in D10 why cant the formula accept with value in D5

Rapchik

 

[etaf]

it can
but you have
(MID($D5,COLUMN(.,.....

so when you copy , because you dont have a $ infront of the 5 - to fix the row reference , that row will change to 10 , when copied into row 10

if you put a $ next to the row as well as the column
=IFERROR(MID($D$5,COLUMN()-(COLUMN($D5)-0),1)+0,0)

now it wont matter wont row you copy the formula into -
so long as the correct column , as mentioned before

 

[RapchikM]

if you put a $ next to the row as well as the column
=IFERROR(MID($D$5,COLUMN()-(COLUMN($D5)-0),1)+0,0)

The above worked : with

-(COLUMN($D5)-0),1)+0,0)

if you observe Still we are writing the formula next to Adjacent Column

But what if the Formula is shifted to L5:O5 with Values in D5
I tried to incorporate in range L5:O5 still it displayed 0 or Zero

Rapchik

 

[etaf]

I'm not really following, or else my explanation is poor - so sorry about that

$D5 - will keep the reference cell for MID to always look at column D , because you have used absolute address for the column $D
but you have used relative referencing for the row - so as you change rows , it will always change the row number

if you put the formula in cell E10 - change of row
then the reference will be
$D10
you are using a mid function
=mid( cell to look-in, start number - starting at character , number of characters from the start number , Length )
=MID( the cell to look in - which is $D5 or $D10
then its the start number .
COLUMN()-(COLUMN($D5)-0)
which character to start looking at - this number is based on your formula using columns() as a reference
column() will change - depending on which column the formula is in - if its in E - then that will return a 5
if its in column L , then returns a 12
now you subtract the column reference for column D - as thats fixed with a $ COLUMN($D5) - which will always be a 4
when the formula is in column E
5-4 which = 1 , then you are subtracting 0 - which will turn text into a real number
which you probably dont need as column() returns real numbers
if the formula was in column L
then it would be 12-4 = 8
so start the lookup at character 8 - but the contents of the cell only has 4 characters
and so the 8th character will return a blank
the last part is 1 which is the how many characters to return from character 8 which is 1 - and so still blank
then you go on to add a zero
MID($D5,COLUMN()-(COLUMN($D5)-0),1)+0
so now where is blank you add a zero
and so all the results in L5 to O5 will be zero

whne in cell L5
MID($D5,COLUMN()-(COLUMN($D5)-0),1)+0
which converted to numbers is
the same as typing
MID($D5,8,1)+0

in M5
MID($D5,9,1)+0

in N5
MID($D5,10,1)+0

in O5
MID($D5,11,1)+0

so all return 0 because of the +0 - otherwise would be blank

hope that helps