copy,paste,calculate Cell value based on calculations & comparisonsother cells same row. Decimal places

[DocAElstein]

OK, take your time.
( I am busy until late tommorrow )

[fixer]

Doc Sir plz see the sample file Sir
Each & Every details is mentioned with examples sir

[fixer]

No Problem Doc Sir take ur time

---

Moderator Translation:
I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a total fucking spacko ... and never mind anyway, fuck off you mug, as some other twat gave me a macro at one of my many duplicated cross postings.......( Intro to Avinash Singh... https://excelfox.com/forum/showthrea...h-Introduction )

[DocAElstein]

Hi
Your file helps , but only a little bit..
I still do not understand from your explanations… The problem is your explanations make no sense in English…
( In next post I try to guess what you want http://www.excelfox.com/forum/showth...ll=1#post13099 )

from your uploaded file explanations . This I do understand

compare column H with column D
there is only two option column H will be greater than column D or column H will be lower than column D
If column H is greater than column D then with column K do the addition after decimal & make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore
If column H is lower than column D then with column K do the subtraction after decimal & make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore
example

If column H is greater than column D then with column K do the addition after decimal & make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore
column H is greater than column D
so now we will check column K data is perfect or not means after decimal the second number is 5 or 0, here the data in column K is 264.47 so the second number after decimal is 7 but we need 5 or 0 ,so as per condition if column H is greater than column D then we have to do addition we cant do subtraction so with 264.47 (264.48,264.49,264.50) so we got first 264.50
so the data will be in column K 264.50

If column H is lower than column D then with column K do the subtraction after decimal & make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore
column H is lower than column D
so now we will check column K data is perfect or not means after decimal the second number is 5 or 0, here the data in column K is 1090.69 so the second number after decimal is 9 but we need 5 or 0 ,so as per condition if column H is lower than column D then we have to do subtraction we cant do addition so with 1090.69(1090.68,1090.67,1090.66,1090.65) so we got first 1090.65
so the data will be in column K 1090.65

wheather column H is greater than column D or column H is lower than column D
if after decimal the second number in column K has data ending with 0 or 5 already then don’t change the data
see the blue colour data
here column K has 147.95 so after decimal the second number is 5 so we will not do anything with column K data

wheather column H is greater than column D or column H is lower than column D
if column K doesn’t have decimal then don’t change the data
here column K has 30 so it doesn’t have decimal so we will not do anything with column K data


All this I done for you: http://www.excelfox.com/forum/showth...ll=1#post13076
Again
Before
_____ Workbook: SAMPLE1 18Apr2020.xlsx ( Using Excel 2007 32 bit )

Row\Col	K
2	1090.69
3	147.95
4	264.47
5	30

After
_____ Workbook: SAMPLE1 18Apr2020.xlsx ( Using Excel 2007 32 bit )

1090.65

147.95

264.5

30

Formula solution from here: http://www.excelfox.com/forum/showth...ll=1#post13076
_____ Workbook: SAMPLE1 18Apr2020.xlsx ( Using Excel 2007 32 bit )

21813.8	21813	1090.65	1090.65	1090.65
2959	2959	147.95	147.95	147.95
5289.4	5289	264.45	264.45	264.45
600	600	30	30	30

---

I do not understand …
What is Do addition ???
What is Do subtraction ??

I still do not understand what has H and D to do with the question????
I see no relevance to column H and column D to the problem
what does it mean...
do the subtraction
do the addition

How does column H and column D effect the answer ???

What does all the addition and subtraction mean ???
what does it mean...
do the subtraction
do the addition

How does column H and D effect the problem ??????

---

From your explanations it is very difficult to understand anything
But…

In next post I try to guess what you want http://www.excelfox.com/forum/showth...ll=1#post13099

[DocAElstein]

The problem is again , as always , that you cannot explain your problem in English.
Also you did not take enough care with your choice of original test data. You original test data did not test all scenarios.
You do not take enough time to prepare your question. This means that we both waste a lot of time later.
If you continue to give such bad information, it will not be possible for me to find lots of time to waste trying to understand your question!!

---

I will make a guess at the question:

This is my guess at what you want:
Explanation 1
In column K are numbers given to a maximum of 2 decimal places, for example
Column K
1090.69
147.95
264.47
30

The value in Column K must be adjusted so that it has the decimal format to 2 decimal places in steps of .05
So in this form, of like
23.95
234
34.25
4.30
100.35
45.45
56.05 ……… etc….

So for example, in the above Column K test data, no adjustment is needed for 147.95 or 30
For 1090.69 and 264.47 some adjustment is needed. The adjustment could be to raise or lower the value. These are the possibilities:
change 1090.69 to 1090.65 or 1090.7
change 264.47 to 264.45 or 264.50

Which of the two adjustments is necessary will depend on the following:
If column H is greater than column D , then we adjust up .
If column H is lower than column D, then we adjust down .

Explanation 2
For all data rows, we compare column H to column D. If column H is greater than column D , then we adjust the value in column K up to the nearest multiple of .05. If column H is less than column D , then we adjust the value in column K down to the nearest multiple of .05. ( If the value in column K is an exact multiple of .05, then no action is to be taken )

_.________

For example
Before:

Row\Col	D	E	F	G	H	I	J	K
2	1087	1088	1077.25	1067.25	1079.9	25	10.799	1090.69
3	148.05	149.9	146.5	146	146.5	22	1.465	147.95
4	265	269.3	265	262.85	267.15	15083	2.6715	264.47
5	30.4	30.4	29.8	29.65	29.95	17388	0.2995	30

After:

Row\Col	D	E	F	G	H	I	J	K	L
2	1087	1088	1077.25	1067.25	1079.9	25	10.799	1090.65	This nuber is adjusted down
3	148.05	149.9	146.5	146	146.5	22	1.465	147.95	This number is not changed
4	265	269.3	265	262.85	267.15	15083	2.6715	264.5	This number is adjusted up
5	30.4	30.4	29.8	29.65	29.95	17388	0.2995	30	This number is not changed

---

Solution ( guess )
See next post:

[DocAElstein]

[color="#3E0000"]


Solution ( guess )
The previous formula solution already always adjust number down,

Row\Col	D	H	K	L	M	N	O	P
2	1087	1079.9	1090.69	21813.8	21813	1090.65	1090.65	1090.65
3	148.05	146.5	147.95	2959	2959	147.95	147.95	147.95
4	265	267.15	264.47	5289.4	5289	264.45	264.45	264.45
5	30.4	29.95	30	600	600	30	30	30

Row\Col	D	H	K	L	M	N	O	P
2	1087	1079.9	1090.69	=K2*100/5	=INT(L2)	=M2*5/100	=INT(L2)*5/100	=INT(K2*100/5)*5/100
3	148.05	146.5	147.95	=K3*100/5	=INT(L3)	=M3*5/100	=INT(L3)*5/100	=INT(K3*100/5)*5/100
4	265	267.15	264.47	=K4*100/5	=INT(L4)	=M4*5/100	=INT(L4)*5/100	=INT(K4*100/5)*5/100
5	30.4	29.95	30	=K5*100/5	=INT(L5)	=M5*5/100	=INT(L5)*5/100	=INT(K5*100/5)*5/100

The above solution is .05 too small for row 4

So previous solution is correct if H < D
If H > D , the previous solution is .05 too small , so previous solution must be adjusted by +.05 if H > D

=IF(H2K2*100/5)*5/100,IF(H2>D2,(INT(K2*100/5)*5/100)+0.05,"H is equal to D"))

=IF(H3K3*100/5)*5/100,IF(H3>D3,(INT(K3*100/5)*5/100)+0.05,"H is equal to D"))

=IF(H4K4*100/5)*5/100,IF(H4>D4,(INT(K4*100/5)*5/100)+0.05,"H is equal to D"))

=IF(H5K5*100/5)*5/100,IF(H5>D5,(INT(K5*100/5)*5/100)+0.05,"H is equal to D"))

But we must also check if number is already exact multiple of .05
Like if ( integer (value/.05))

[fixer]

Thnx Doc Sir for showing ur great interest to solve this problem
& Doc Sir as u know i am not that smart that i can make this complicated vba code for me so plz help sir

---

Moderator Translation:

I cant speak , write or understand a word of English I haven’t a clue what is written in this Thread, most of the time I haven’t got a clue what I wont and rarely even understand what I write myself, so I will post any rubbish and hope magically a code appears here that when I run it will do what I want. If not fuck the twat helping me here: I have duplicated cross posted all over the place so maybe from there something will come of it.

[DocAElstein]

.. as u know i am not that smart

..You are smart enough to get others to do your work .. maybe
आपण मुर्ख नाही कारण आपण इंग्रजीमध्ये संप्रेषण करू शकत नाही. मी मराठी किंवा हिंदी बोलू शकत नाही. पण या कारणास्तव मी मूर्ख नाही

I try this as the Question…..
Explanation 2
For all data rows, we compare column H to column D. If column H is greater than column D , then we adjust the value in column K up to the nearest multiple of .05. If column H is less than column D , then we adjust the value in column K down to the nearest multiple of .05. ( If the value in column K is an exact multiple of .05, then no action is to be taken )

VBA answer

Put columns in arrays

Row\Col	D
1	Open
2	1087
3	148.05
4	265
5	30.4

arrD() =

1087

148.05

265

30.4

Row\Col	H
1	LTP
2	1079.9
3	146.5
4	267.15
5	29.95

arrH() =

1079.9

146.5

267.15

29.95

Row\Col	K
1
2	1090.69
3	147.95
4	264.47
5	30

arrK() ( initial ) =

1090.69

147.95

264.47

30

The macro below manipulates the contents of arrK() as per the question requirement, then pastes the modified array over the initial values
Macro here: http://www.excelfox.com/forum/showth...ll=1#post13105

After running that macro, Sub ChangeSecondNumberAfterDecimalConditionally() , the arrK() contents change to

1090.65

147.95

264.5

30

And that is then pasted out into the range

Row\Col	K
1
2	1090.65
3	147.95
4	264.5
5	30

[DocAElstein]

Another obvious solution which I missed…

Row\Col	K	L
2	1090.699	109069
3	147.965	14796
4	264.4785	26447
5	30.2495	3024

Row\Col	K	L
2	1090.699	=INT(K2*100)
3	147.965	=INT(K3*100)
4	264.4785	=INT(K4*100)
5	30.2495	=INT(K5*100)

---

And thank you Mr Sandy sir, for your interesting alternative solution

[fixer]

Thnx Sandy Sir for ur great help