How to make shift for the decimals

khallod

New Member
Joined
Jun 2, 2019
Messages
3
[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]

xy
1.515
2.630
3.110
4.220
5.710
6.220
7.130
8.315

<tbody>
</tbody>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,796
Office Version
  1. 365
  2. 2010
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

New Member
Joined
Jun 2, 2019
Messages
3
it is 30. but after adding 5 from the first cell in Y , it will be 35
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,948
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 17, 2009
Messages
5,182
Office Version
  1. 2013
Platform
  1. Windows
Does this do it?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;;">Orig X</td><td style="text-align: center;;">Orig Y</td><td style="text-align: center;;"></td><td style="text-align: center;;">New X</td><td style="text-align: center;;">New Y</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">1.5</td><td style="text-align: center;;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">2.6</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">33.9024</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">3.1</td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">9.56978</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">4.2</td><td style="text-align: center;;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;">18.8962</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">5.7</td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;">12.1345</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;;">6.2</td><td style="text-align: center;;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;">19.1451</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;;">7.1</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">29.5756</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">8.3</td><td style="text-align: center;;">15</td><td style="text-align: right;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;">14.0631</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Jan</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">A2="","",A2+MOD(<font color="Red">SUM(<font color="Green">A$1:A1</font>),1</font>)-MOD(<font color="Red">SUM(<font color="Green">A$1:A2</font>),1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">A2="","",(<font color="Red">SUM(<font color="Green">D$1:D2</font>)/SUM(<font color="Green">A$1:A2</font>)*SUM(<font color="Green">B$1:B2</font>)</font>)-SUM(<font color="Red">E$1:E1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Hope that helps.
 

khallod

New Member
Joined
Jun 2, 2019
Messages
3
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,130,281
Messages
5,641,303
Members
417,203
Latest member
cheesegeneman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top