How to make shift for the decimals

[khallod]

[FONT=&quot]if I have two columns, let me assume its name as X and Y. The X column has decimals in every cell value. --> I want to make the X values as integer numbers by adding the decimal part to the next cell value until the end of the column. --> sure the decimal part present a percentage from the original cell value, So I want to shift the same percentage from the Y column to the next cell.[/FONT]
[FONT=&quot]for example: if the X and Y columns as follow:[/FONT]
[FONT=&quot]in this case for the X Column values--> the first values will be in values of 1, and the 0.5 will be added to the next cell (2.6), now the cell (2.6) will be (2.6 + 0.5 = 3.1) so the second cell will in value of 3 and the part 0.1 will be added to the next cell ... and so on.[/FONT]
[FONT=&quot]But, in the first cell, we shifted 0.5, its = 33% from the original value, so it's required to shift also 33% from the opposite Y value to the next one, in this case, the first cell from Y column will be (15-5=10), and the value of 5 will be added to the next cell (30) to be (30+5=35) in the second Y Cell.[/FONT]
[FONT=&quot]summary: -->shift the decimals part to the next. (X have to be an integer) --> shift the Y values by the same percentage (not required to be an integer or not)


[/FONT]
[TABLE="width: 500"]
<tbody>[TR]
[TD]x[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]1.5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2.6[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3.1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4.2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5.7[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6.2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]7.1[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]8.3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kweaver]

Not quite following this because the 2nd value in the Y column you posted is a 30 when your text implies it should be 35?

 

[khallod]

it is 30. but after adding 5 from the first cell in Y , it will be 35

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...8095-shift-the-decimals-to-the-next-cell.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Snakehips]

Does this do it?

Excel 2010
	A	B	C	D	E
1	Orig X	Orig Y		New X	New Y
2	1.5	15		1	10
3	2.6	30		3	33.9024
4	3.1	10		3	9.56978
5	4.2	20		4	18.8962
6	5.7	10		6	12.1345
7	6.2	20		6	19.1451
8	7.1	30		7	29.5756
9	8.3	15		8	14.0631
10
Jan

Cell Formulas
Range	Formula
D2	=IF(A2="","",A2+MOD(SUM(A$1:A1),1)-MOD(SUM(A$1:A2),1))
E2	=IF(A2="","",(SUM(D$1:D2)/SUM(A$1:A2)*SUM(B$1:B2))-SUM(E$1:E1))

Hope that helps.

 

[khallod]

Can you help in this please ??

I have the table as attached.

the columns G and H were performed manually.
--> I want to look for the matching values. and its opposite cells
--> the matching required between Column A and Column E.
--> generate a new column X3--> generate new column Y3



For example:if D2=A2, then, I want to put G2=A2 or D2 (they are the same). and put H3=B2+E2if D2, not equal A2, in this case, I have 2 options :
1- looking for matching between D2 and the next cell in column A (it will be now A3), if D2=A3, then put G2=D2 or A3 (they are the same). and put H3=B3+E2.
2- if D2, not equal A4, then transfer to the next A4, then A5 and so on until finding the matching.
3-if D2 doesn't equal any value in the column A, then put G2=D2 and put H3=E3



and Also, If a value in the column A does not match with any values in the column D, for example (A13=95) then put the cell (G15) = A13 and H15=B13.



Do the same with the Cell D3



fortunately the columns A and D always in increasing order



summary:
-->generate new X3 and Y3,
-->X3 contains the smallest one of the cell's content in X1 and X2
-->Y3 contains the sum of Y1 and Y2 if X1 cell=X2 cell, or = the opposite cell of Y2

A	B	C	D	E	F	G
82	10		82		82	12
83	20		83		93	22
84	30		84		84	32
86	10		85		85	3
87	20		86		86	16
88	30		87		87	26
89	10		88		88	35
90	5		89		89	14
92	6		90		90	13
93	10		91		91	5
94	20		92		92	6
95	20		93		93	12
97	20		94		94	23
98	10		96		95	20
99	30		97		96	24
100	5		98		97	25
101	5		99		98	15
102	5		100		99	36
103	10		101		100	10
					101	9
					102	5
					103	10

<thead>
</thead> <tbody>
</tbody>