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

---

“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthrea...ll=1#post14972
https://excelfox.com/forum/showthrea...ll=1#post14972
I am no longer monitoring what its doings. It had curiosity appeal for a while, but even that has worn off me now!
It’s getting worse by the Day. Its still doing whatever it is that it is doing and getting Replies and answers at excelforum.com and likely a few places I don’t know about.

---

---

vba code will be placed in a seperate file macro.xlsm
i have one more file 1.xls (all files are located in a different path)
i have attached a sample pic of 1.xls & result pic too
check if Column H is greater or lower than column D
if column H is greater than column D then calculate 1% of column H and paste it to column J and then subtract Column H & column J and paste the result to column K
Or
if column H is lower than column D then calculate 1% of column H and paste it to column J and then add Column H & column J and paste the result to column K
Plz help me in solving this problem sir

---

Almost the same as from MoldyBread at excelforum
https://www.excelforum.com/excel-pro...ml#post5338184

---

Some more recent cross posted duplicsate on another one of Avinashes infinite cycles of starting again
https://eileenslounge.com/viewtopic.php?f=30&t=34932
https://chandoo.org/forum/threads/pu...9/#post-266257 ( Leonardo1234 starting again )

This is so simple, that even I find it easy to do, with a formula...



_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1087	1030	955.5	998.45	1079.9	22	=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))	=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))
3	NSE	ADANIPOWER	EQ	148.05	27.75	25.65	25.65	146.5	25	=IF(H3>D3,1/100*H3,IF(H3<D3,1/100*H3,"D is equal to H"))	=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H"))
4	NSE	DLF	EQ	265	419.7	350.05	387.25	267.15	17388	=IF(H4>D4,1/100*H4,IF(H4<D4,1/100*H4,"D is equal to H"))	=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H"))
5	NSE	AMBUJACEM	EQ	30.4	155.8	142.55	145.85	29.95	15083	=IF(H5>D5,1/100*H5,IF(H5<D5,1/100*H5,"D is equal to H"))	=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H"))

Worksheet: 1-Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1087	1030	955.5	998.45	1079.9	22	=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))	=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))
3	NSE	ADANIPOWER	EQ	148.05	27.75	25.65	25.65	146.5	25	=IF(H3>D3,1/100*H3,IF(H3<D3,1/100*H3,"D is equal to H"))	=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H"))
4	NSE	DLF	EQ	265	419.7	350.05	387.25	267.15	17388	=IF(H4>D4,1/100*H4,IF(H4<D4,1/100*H4,"D is equal to H"))	=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H"))
5	NSE	AMBUJACEM	EQ	30.4	155.8	142.55	145.85	29.95	15083	=IF(H5>D5,1/100*H5,IF(H5<D5,1/100*H5,"D is equal to H"))	=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H"))

Worksheet: 1-Sheet1

Each formula uses 2 Excel If functions, one is nested in the other...

Code:

---

(    If  _>_  , Do this ,  Else [ If _<_ , Do this  , Else "......"    ]    )

    If ( _>_  , Do this ,  Else If( _<_ , Do this  , Else "......"    )    )

---

Column J

Code:

---

  =IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"d is equal to H"))

if column H is greater than column D then calculate 1% of column H
  =IF(H2>D2,1/100*H2,
                    Else
          if column H is lower than column D then calculate 1% of column H
                    IF(H2<D2,1/100*H2,

                                    Else
                                      "D is equal to H"))

---

Column K

Code:

---


  =IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))

if column H is greater than column D subtract Column H & column J
  =IF(H2>D2,H2-J2,
                  Else
          if column H is lower than column D  add Column H & column J
                  IF(H2<D2,H2+J2,


                                  Else
                                  "D is equal to H"))

---

Alan

Ref
https://support.office.com/en-us/art...rs=en-US&ad=US
https://www.techonthenet.com/excel/formulas/if.php

Code:

---

Sub STEP8()
  Dim Wb1 As Workbook, Ws1 As Worksheet, Lr1 As Long
  Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
  Set Ws1 = Wb1.Worksheets.Item(1)
  Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
  Ws1.Range("J2:J" & Lr1 & "").Value = "=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"d is equal to H"))"
  Ws1.Range("J2:J" & Lr1 & "").Value = Ws1.Range("J2:J" & Lr1 & "").Value
  Ws1.Range("K2:K" & Lr1 & "").Value = "=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))"
  Ws1.Range("K2:K" & Lr1 & "").Value = Ws1.Range("K2:K" & Lr1 & "").Value
  Wb1.Save
  Wb1.Close
End Sub

---

Doc Sir i am getting error sir i dont know why i got it plz have a look sir

---

http://www.eileenslounge.com/viewtopic.php?f=30&t=34670

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hi
Your macro is very good.
You have written good coding.
It is almost perfect.

There is just one very small problem. It is just because VBA is confused when quotes are inside quotes. VBA does not like to see quotes inside other quotes. It get confused.

If VBA see this: …_

Code:

---

    "    "    "

---

_.. VBA does not know what it is. VBA thinks maybe it is either this

Code:

---

    "    "    "

---

or this

Code:

---

    "    "    "

---

VBA does not like to see inside quotes 1 quote or 3 quotes or 5 quotes … etc

VBA must always see 2 quotes "" ( or 4 quotes m"""", or 6 quotes """""" )etc..
We must do a trick , like this

Code:

---

    "    ""    "

---

Or this:

Code:

---

    "    ""    ""    "

---

If you do this trick, then VBA will see for "" just one quote.

So in your macro , like this is the problem: "________"_D is equal to H_"____"

Code:

---

"=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))"

---

We must do trick: "________""_D is equal to H_""____"

Code:

---

"=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,""D is equal to H""))"

---

So in your macro,
change the single quotes inside the outermost enclosing quotes to double quotes
like this:
______________Ws1.Range(" 2: " & Lr1 & "").Value =__"__________""_D is equal to H_""____"

Code:

---

Sub STEP8()
  Dim Wb1 As Workbook, Ws1 As Worksheet, Lr1 As Long
  Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
  Set Ws1 = Wb1.Worksheets.Item(1)
  Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
  Ws1.Range("J2:J" & Lr1 & "").Value = "=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,""D is equal to H""))"
  Ws1.Range("J2:J" & Lr1 & "").Value = Ws1.Range("J2:J" & Lr1 & "").Value
  Ws1.Range("K2:K" & Lr1 & "").Value = "=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,""D is equal to H""))"
  Ws1.Range("K2:K" & Lr1 & "").Value = Ws1.Range("K2:K" & Lr1 & "").Value
  Wb1.Save
  Wb1.Close
End Sub

---

You did almost perfect in your macro. It was very close.

Alan

---

http://www.eileenslounge.com/viewtopic.php?f=30&t=34670

I will place the macro in a seperate file macro.xlsm
and i have a file name 1.xls
in 1.xls in column K i have data (i have attached the sample pic of that)
what i need is i need a macro that will remove third number after decimal and along with it it should remove the decimal
Result will be
1090.699 after runing the macro the ouput will be 109069
147.965 after runing the macro the ouput will be 14796
264.4785 after runing the macro the ouput will be 26447
30.2495 after runing the macro the ouput will be 3024

plz help me in solving this problem by vba

Thnx Alot Doc Sir for helping me in solving this problem

Using simple VBA techniques
' We can find the position of the . using Instr function https://docs.microsoft.com/en-us/off...instr-function
' Then we can take the left of the number for a length equal to the position of the . + 2 using the Left function https://docs.microsoft.com/en-us/off.../left-function
' Then we can remove the . using the Replace function , https://docs.microsoft.com/en-us/off...place-function

Code:

---

Sub TrimRemoveDot() '  http://www.excelfox.com/forum/showthread.php/2456-Remove-decimals-by-vba?p=13068#post13068
Dim Ws1 As Worksheet
 Set Ws1 = Workbooks("1.xls").Worksheets.Item(1) ' First worksheet in open workbooks 1.xls
 Dim LrK As Long: Let LrK = Ws1.Range("K" & Ws1.Rows.Count & "").End(xlUp).Row
Dim RngK As Range: Set RngK = Ws1.Range("K2:K" & LrK & "")
Dim SnglCel As Range
    For Each SnglCel In RngK
    Dim Pos As Long: Let Pos = InStr(1, SnglCel.Value, ".", vbBinaryCompare)      '    We can find the position of the . using Instr function                                                            https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function
    Let SnglCel.Value = Left(SnglCel.Value, Pos + 2)                            '    Then we can take the left of the number for a length equal to the position of the . + 2 using the Left function  https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/left-function
    Let SnglCel.Value = Replace(SnglCel.Value, ".", "", 1, -1, vbBinaryCompare)  '    Then we can remove the . using the Replace function                                                              https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function
    Next SnglCel
End Sub

---

---

Or using formulas
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	K
1
2	1090.699
3	147.965
4	264.4785
5	30.2495
6

Worksheet: 1-Sheet1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	L	M	N	O
8	=FIND(".",K2)	=LEFT(K2,L8+2)	=SUBSTITUTE(M8,".","")	=SUBSTITUTE(LEFT(K2,FIND(".",K2)+2),".","")
9	=FIND(".",K3)	=LEFT(K3,L9+2)	=SUBSTITUTE(M9,".","")	=SUBSTITUTE(LEFT(K3,FIND(".",K3)+2),".","")
10	=FIND(".",K4)	=LEFT(K4,L10+2)	=SUBSTITUTE(M10,".","")	=SUBSTITUTE(LEFT(K4,FIND(".",K4)+2),".","")
11	=FIND(".",K5)	=LEFT(K5,L11+2)	=SUBSTITUTE(M11,".","")	=SUBSTITUTE(LEFT(K5,FIND(".",K5)+2),".","")

Worksheet: 1-Sheet1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	L	M	N	O
8	5	1090.69	109069	109069
9	4	147.96	14796	14796
10	4	264.47	26447	26447
11	3	30.24	3024	3024

Worksheet: 1-Sheet1

---

Or using a final formula in Evaluate Range One liner technique

Code:

---

Sub EvaluateRangeTrimRemoveDot() '  http://www.excelfox.com/forum/showthread.php/2456-Remove-decimals-by-vba?p=13068#post13068
Dim Ws1 As Worksheet
 Set Ws1 = Workbooks("1.xls").Worksheets.Item(1) ' First worksheet in open workbooks 1.xls
 Dim LrK As Long: Let LrK = Ws1.Range("K" & Ws1.Rows.Count & "").End(xlUp).Row
Dim RngK As Range: Set RngK = Ws1.Range("K2:K" & LrK & "")
 Let RngK.Value = Evaluate("=if({1},SUBSTITUTE(LEFT(" & RngK.Address & ",FIND("".""," & RngK.Address & ")+2),""."",""""))")
End Sub

---

Alan

---

Some more recent cross posted duplicsate on another one of Avinashes infinite cycles of starting again
https://eileenslounge.com/viewtopic.php?f=30&t=34932
https://chandoo.org/forum/threads/pu...9/#post-266257 ( Leonardo1234 starting again )

Thnx Alot Doc Sir for ur Great Support
Problem Solved

all file are located in a different path
vba code will be placed in a seperate file macro.xlsm
my file name is 1.xls
If column H of 1.xls is lower than column D of 1.xls then with column K (subtract it and make it to the number ending with 0 or 5 whichever met first)
If column H of 1.xls is greater than column D of 1.xls then with column K (add it and make it to the number ending with 0 or 5 whichever met first)
i have attached the sample file and result too.. plz have a look sir
and help me in solving this problem sir

just for fun with Power Query

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"raw", type text}}, "en-GB"), "raw", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"raw.1", "raw.2"}),
    Extract = Table.TransformColumns(Split, {{"raw.2", each Text.Start(Text.From(_, "en-GB"), 2), type text}}),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Extract, {{"raw.1", type text}}, "en-GB"),{"raw.1", "raw.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result"),
    Type = Table.TransformColumnTypes(Merge,{{"Result", Int64.Type}})
in
    Type

---

raw	Result
1090.699	109069
147.965	14796
264.4785	26447
30.2495	3024

should be ok
thanks Alan :cool:

Hi
:confused:
I do not understand your question ??
I do not understand what you want.??

Quote:

---

Originally Posted by fixer

If column H of 1.xls is lower than column D of 1.xls then with column K (subtract it and make it to the number ending with 0 or 5 whichever met first)
If column H of 1.xls is greater than column D of 1.xls then with column K (add it and make it to the number ending with 0 or 5 whichever met first)r

---

:confused: :confused:
That makes no sense. ?? It is rubbish - In English it makes no sense.

Your samples do not explain anything which happens to column K as a result of H or D. You explanation and your sample data do not seem to have any relevance. :confused:
Your explanation is no use.

---

But I can get your results with some maths… All of this is based on maths for column K. I see no relevance to column H or D ??

From your data

Before	Result After
1090.69	1090.65
147.96	147.95
264.47	264.45
30.24	30.2

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	J	K	L	M	N	O	P	Q
1
2		1090.69	=K2*100/5	=INT(L2)	=M2*5/100	=INT(L2)*5/100	=INT(K2*100/5)*5/100
3		147.96	=K3*100/5	=INT(L3)	=M3*5/100	=INT(L3)*5/100	=INT(K3*100/5)*5/100
4		264.47	=K4*100/5	=INT(L4)	=M4*5/100	=INT(L4)*5/100	=INT(K4*100/5)*5/100
5		30.24	=K5*100/5	=INT(L5)	=M5*5/100	=INT(L5)*5/100	=INT(K5*100/5)*5/100
6

Worksheet: Tabelle1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	J	K	L	M	N	O	P	Q
1
2		1090.69	21813.8	21813	1090.65	1090.65	1090.65
3		147.96	2959.2	2959	147.95	147.95	147.95
4		264.47	5289.4	5289	264.45	264.45	264.45
5		30.24	604.8	604	30.2	30.2	30.2
6

Worksheet: Tabelle1

Column H & Column D has relation bcoz Either Market can Move Up Or Down ,I have to Buy shares Or i have to sell shares
& why i need macro is
1090.69
147.96
264.47
30.24

After decimal if the number doesnt ends with .05 or .0 then that order is not accepted by system

That does not explain anything about the question you asked. Once again we have the problem that you cannot communicate in English language.

I still do not understand
I still do not understand the relevance to the question http://www.excelfox.com/forum/showth...ll=1#post13074 . … I see not relation to your test data in H and D and the results which you ask for

I have no idea what you are asking for. :confused:
I have given you the results you asked for, but it has nothing to do with anything in column H and D. My results are what you asked for, but I do not consider anything in column H and D.

if column H of 1.xls is lower than column D of 1.xls then with column K do the subtraction after decimal and make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore that cell
If column H of 1.xls is greater than column D of 1.xls then with column K do the addition after decimal and make it to the number ending with 0 or 5 whichever met first and if it ends with .05 or .00 then ignore that cell

this is the conditon Doc Sir

Sorry, I do not understand
What you wrote makes no sense in English.

Try to explain again with lots more detailed data.

Your screenshots are almost useless. They explain only the problem which I answered. They explain nothing about column H or D. They show only a change in column K. There is no explanation from you about the relavance to the question and column H or D.

You explanations so far are almost useless. They say almost nothing. Or they make no sense.. what does it mean...
do the subtraction :confused:
do the addition :confused:

column H is a Last traded price & column D is a opening price
from this we came to know about market is moving up or moving down in that situation , u cant rely on high or low bcoz it always makes a new high & new low
Doc Sir what information u need plz specify that so that i can answer the same bcoz i havent understood ur question yet sir

---

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 )

Doc Sir as u said u understood the problem
so plz help me to solve this problem Sir

It is almost impossible to communicate with you.

---

You are telling me things which appear to have no relevance to your question.
This I understand:
… column H is a Last traded price & column D is a opening price
from this we came to know about market is moving up or moving down in that situation , u cant rely on high or low bcoz it always makes a new high & new low
But I see no relevance to the question??


It is impossible to help you further. I cannot guess what you want.

Quote:

---

Originally Posted by fixer

Doc Sir as u said u understood the problem
so plz help me to solve this problem Sir

---

No . I did not say that. I do not understand the problem !!!!
what does it mean...
do the subtraction :confused:
do the addition :confused:

I will look again tomorrow.
If , before tomorrow, you have explained fully , with test data what you want, then I will try to help further.

If, by tomorrow you have not explained fully what you want, then I will delete this Thread because it is full of rubbish.

Alan

---

Quote:

---

Originally Posted by fixer

Doc Sir what information u need plz specify that so that i can answer the same bcoz i havent understood ur question yet sir

---

My question is simple.

What do you want? What is your question?
So far all your explanations make no sense in English language

You tell me things about column H and D. But you do not tell me in English what is the relavance to your results and question

what does it mean...
do the subtraction :confused:
do the addition :confused:

Doc Sir plz give me 24hrs from now due to internet issue
i will share the sample file with all details in it
thnx for the support sir

---

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 )

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	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 … :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	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:

[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(H2<D2,INT(K2*100/5)*5/100,IF(H2>D2,(INT(K2*100/5)*5/100)+0.05,"H is equal to D"))

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

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

=IF(H5<D5,INT(K5*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))

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.

Quote:

---

Originally Posted by fixer

.. 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

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 ;) :) :cool:

Thnx Sandy Sir for ur great help

..You are smart enough to get others to do your work
Yes I am Smart enough Doc Sir to get others to do my work
But No Doubt Doc Sir The work that they do is priceless
If i were smart then i can make a vba code for me %O

Problem Solved Doc Sir thnx Alot for giving ur Precious time & Great Support

Calculate 2% of colum H & column I & considered the greater number between them
column S should be positive, so don't considere the no. which are negative
& if column S is lower than that 2% of column H or Column I (whichever is greater )then put -1
vba macro will be placed in a seperate file , sheet name can be anything, all files are located in different place
example
the U2 cell will become -1 after runing the macro

---

Similar to this from MoldyBread at excelforum
https://www.excelforum.com/excel-pro...ml#post5338184

Hi

Before

_____ Workbook: ap.xls ( Using Excel 2007 32 bit )

Row\Col	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	NetBuyValue	NetSellValue	NetValue	NetBuyQty	NetSellQty	NetQty	BEP	SellAvgPrice	BuyAvgPrice	LastTradedPrice	MarkToMarket	Realized MarkToMarket	Unrealized MarkToMarket	EL MarkToMarket
2	10781.10	10878.30	97.20	54	54			201.45	199.65	201	97.2	97.2		97.2
3	420.60	430.50	9.90	2	2			215.25	210.30	210.35	9.9	9.9		9.9
4	2429.10	2405.70	-23.40	18	18			133.65	134.95	135	-23.4	-23.4		-23.4
5	6120.90	6064.20	-56.70	54	54			112.30	113.35	111.6	-56.7	-56.7		-56.7
6	66.30	65.70	-0.60	2	2			32.85	33.15	32.5	-0.6	-0.6		-0.6
7	15114.60	14966.10	-148.50	54	54			277.15	279.90	279.15	-148.5	-148.5		-148.5
8	570.60	567.00	-3.60	6	6			94.50	95.10	93.35	-3.6	-3.6		-3.6
9	7344.00	7272.40	-71.60	54	54			134.67	136.00	138.5	-71.6	-71.6		-71.6
10

Worksheet: ap-Sheet1

After

_____ Workbook: ap.xls ( Using Excel 2007 32 bit )

Row\Col	U
1	EL MarkToMarket
2	-1
3	9.9
4	-23.4
5	-56.7
6	-0.6
7	-148.5
8	-3.6
9	-71.6
10

Worksheet: ap-Sheet1


Macro here: https://excelfox.com/forum/showthrea...ll=1#post13424


Alan

Thnx Alot Doc Sir for helping me in solving this problem Sir
Have a Awesome Day

---

1 June : https://www.eileenslounge.com/viewto...p?f=30&t=34710

Hi Experts,
I am looking for a macro that will do the things mentioned below

Check column H of 1.xls has how many number before decimal (If decimal is not present then check how many numbers it has )
after knowing this, match column I of 1.xls with Column B of 2.xlsx & if matched then put decimal in column E of 2.xlsx after that many numbers (if decimal is not present) & if decimal is present then remove that decimal & reput the decimal in column E of 2.xlsx according to the numbers

Plz see the sample file


Macro will be placed in macro.xlsm
both file can be located anywhere in the pc so the path should be hardcoded in the macro so that i can change it as per my needs in the future

Thnx For the Help

Any Doubts Any Question Plz Ask

I assume that we do nothing if there is no match in data from column I 1.xls in the column B in sample2.xlsx

So, another way of explaining the requirement is
Try to find a match in the data from each row, starting at 2 of column I 1.xls in column B 2.xlsx
If we have a match, then we find the length of the integer of the value for the H ( in variable, LHint ) in 1.xls for that row
Once we have that we first, remove any decimal place in the matched row in 2.xlsx in column E
What we then do is convert that number in the matched row in 2.xlsx in column E to effectively be
_______________= the first LHint characters of it & a decimal point & then the rest of the characters
__ arr2E(MtchRes, 1) = Left(arr2E(MtchRes, 1), LHInt) & "." & Mid(arr2E(MtchRes, 1), LHInt + 1)





Before:
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1265	1282.7	1246.5	1275.3	1247	22	BUY	116761.05
3	NSE	ADANIENT	EQ	151.85	165.45	151.4	151.85	152.35	25	BUY	14104.65
4	NSE	ADANIPORTS	EQ	348	348	338.5	346.55	338.85	15083	BUY	3235.03
5	NSE	ADANIPOWER	EQ	38.85	38.9	37.65	38.8	37.85	17388	SHORT	3469.653
6	NSE	AMARAJABAT	EQ	662.5	665.9	642.55	662.5	643.5	100	BUY	5680.24

Worksheet: 1-Sheet1 18July

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	22	6	<	115605	A					GTT
2	NSE	25	6	<	13965	A					GTT
3	NSE	15083	6	<	3203	A					GTT
4	NSE	17388	6	>	3504.7	A					GTT
5	NSE	100	6	<	5624	A					GTT

Worksheet: Sheet1 18 July

After:

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	22	6	<	1156.05	A					GTT
2	NSE	25	6	<	139.65	A					GTT
3	NSE	15083	6	<	320.3	A					GTT
4	NSE	17388	6	>	35.047	A					GTT
5	NSE	100	6	<	562.4	A					GTT

Worksheet: Sheet1 18 July


Macro Here : https://excelfox.com/forum/showthrea...ll=1#post14674

Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem
Have a Awesome Day Sir

Hi Experts,

I am looking for a macro that will do the the things mentioned below
Macro will be placed in a macro.xlsm
macro.xlsm & 1.xls both are located in different path so the path should be hardcoded in the macro, so i can change it as per my needs


In column K, If after decimal if the second digit is 0 then Move all the data from after decimals to before decimals
K2 has data that has the second number after decimal is 0, so the output would be in K2 10030.00


Thnx For the Help
https://www.excelforum.com/excel-pro...e-decimal.html

Problem Solved

The macro that you obtained from excelforum just multiplies all the column K values by 100. Your description suggests that you did not want just that.