shift the decimals to the next cell

convert the values to integer and add the decimals to the next cell
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.
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.
For example:

if the X and Y columns as follows:

X Y
1.5 15
2.6 30
3.1 10
4.2 20
5.7 10
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.

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.

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)

Hello khallod,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.

Incorrectly posted - moved to the correct sub-forum.

The algorithm seems simple enough, I wonder if there is a specific programming question that I am missing. Here's how I would probably do something like this:

0) Assume X and Y are in columns A and B and reserve row 1 for text headers.
1) Process 1st row X values:
1a) Add "new x" to C1. C2 =A2 (the reason for this will be more apparent later).
1b) Add "integer x" to D1. D2 =INT(C2) (or whatever function you like for the "round to integer" step)
1c) Add "remainder x" to E1. E2 =MOD(C2,1) or =C2-D2
1d) Add "%change" to F1. F2 =E2/C2
2) process 1st row y values.
2a) Add "new y" to G1. G2 =B2.
2b) Add "adjusted y" to H1. H2 =G2*(1+F2)
2c) Add "remainder y" to I1. I2 =G2-H2
3) calculate 2nd row values.
3a) C3 =A3+D2. D3 to F3 are copies of D2:F2.
3b) G3 =B3+H2. H3:I3 are copies of H2:I2
4) Calculate everything else by copying C3:I3 down to the bottom of the data set.

Did I understand the overall algorithm correctly? Will that work for you? Do you have aa specific question about programming that into the spreadsheet?

crosspost: https://www.mrexcel.com/forum/excel-...-decimals.html



Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

Originally Posted by khallod

how can i moved to the correct sub-forum.

please send me the link

I have already moved it for you.

For X in E2

=INT(MOD(SUM($A$1:$A1),1)+A2)

For Y in F2

=$B2*(1- MOD((MOD(SUM($A$1:$A1),1)+A2),1)/(MOD(SUM($A$1:$A1),1)+A2))

Drag down both.

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

Untitled.jpg

Your current sample shows X3 as every integer between 82 and 103 without skipping anything. I would be inclined to simply enter 82 and 83 into G2 and G3 then fill (drag the little plus symbol) down to row 23. https://support.office.com/en-us/art...2-35a236c5b5db

Once you have X3, then Y3 looks like a simple pair of SUMIFS(). Something like =SUM(SUMIFS($B$2:$B$20,$A$2:$A$20,G2),SUMIFS($E$2:$E$20,$D$2:$D$20,G2)) entered in H2 and copy/fill down.