conditionally change the value after decimal

Sachin Singh
Sachin Singh used Ask the Experts™
on
If column J is BUY Then add the number after decimal  so that the second value after decimal comes to 5 or 0  and after it comes to 0 then remove all the value after decimal second value
If column J is SELL then  subtract the number  after decimal so that the second  value after decimal comes to 5 or 0  and after it comes to 0 then remove all the value after decimal  second value

plz see the sample file
OneClick.xlsb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What result do you want if the the number is 1615.0114?
What result do you want if the the number is 1615.6114?

Author

Commented:
If column J  is BUY  then 1615.05
If column J is SELL then  1615.60
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Does this work for you? It assumes that the number to be changed is in column 'Z'. You'll need to format the output column to be a number with 2 decimals.

=IF(J7="BUY",TRUNC(Z7,1)+0.05,TRUNC(Z7,1)+0.6)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes Martin Sir
But i want the vba code to do this  and change the data to column G and column L see the sample file
so plz look into it
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Define your source range and your target range.
Then loop these while copying/setting the values using this function:

Public Function RoundBuySell( _
    ByVal InputValue As Double, _
    ByVal BuySell As String) _
    As Double

    Dim OutputValue As Double
    
    Select Case BuySell
        Case "BUY"
            OutputValue = -Int(-InputValue * 20) / 20
        Case "SELL"
            OutputValue = Int(InputValue  * 20) / 20
    End Select
    
    RoundBuySell = OutputValue
    
End Function

Open in new window

It either rounds up or down as you have requested.

Author

Commented:
Thnx Martin Liss  Sir and Gustav Brock  Sir for ur  Great Support
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Hmm, no points for an excellent function ...

Author

Commented:
i have given both half half Sir
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great! Have a nice day.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial