OK, take your time. :)
( I am busy until late tommorrow )
Printable View
OK, take your time. :)
( I am busy until late tommorrow )
Doc Sir plz see the sample file Sir
Each & Every details is mentioned with examples sir
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 )
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
After
_____ Workbook: SAMPLE1 18Apr2020.xlsx ( Using Excel 2007 32 bit )
Formula solution from here: http://www.excelfox.com/forum/showth...ll=1#post13076
_____ Workbook: SAMPLE1 18Apr2020.xlsx ( Using Excel 2007 32 bit )
I do not understand … :confused:
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 :confused:
do the addition :confused:
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 :confused:
do the addition :confused:
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
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
After:
Row\Col This nuber is adjusted down This number is not changed This number is adjusted up This number is not changed
Solution ( guess )
See next post:
[color="#3E0000"]
Solution ( guess )
The previous formula solution already always adjust number down,
Row\Col
Row\Col
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
But we must also check if number is already exact multiple of .05
Like if ( integer (value/.05))
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.
..You are smart enough to get others to do your work .. maybe :)Quote:
Originally Posted by fixer
आपण मुर्ख नाही कारण आपण इंग्रजीमध्ये संप्रेषण करू शकत नाही. मी मराठी किंवा हिंदी बोलू शकत नाही. पण या कारणास्तव मी मूर्ख नाही
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 Open
arrD() =
Row\Col LTP
arrH() =
Row\Col
arrK() ( initial ) =
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
And that is then pasted out into the range
Row\Col
Another obvious solution which I missed…
Row\Col
Row\Col
And thank you Mr Sandy sir, for your interesting alternative solution ;) :) :cool:
Thnx Sandy Sir for ur great help