• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to convert Cr amount as negative

Status
Not open for further replies.

Prodip Das

New Member
I want to convert Cr. amount as Negative which is exported from Tally accounting software to Excel. Dr. amount will remain as positive. I mean if there is an amount shows as say 100 Dr. i want to show it in excel as 100 or if there is an amount 200 Cr. i want to show it as (-) 200 or (200) as negative.

Please find attached herewith the file.
 

Attachments

  • TrialBal.xls
    20 KB · Views: 91
Because the "Cr" and "Dr" aren't actually part of the cell value, but are in the cell's format, it's impossible to do this using just basic formulas.

If you install this UDF (Right-click on a sheet tab, view code to open Visual Basic Explorer [VBE]. Go to Insert - Module. Paste Code in, then close VBE)
Code:
Function CreditDebit(myCell As Range) As Double
Dim strVal As String
Dim myMult As Integer

strVal = myCell.Text

If Right(strVal, 2) = "Cr" Then
    myMult = -1
Else
    myMult = 1
End If

CreditDebit = myCell.Value * myMult

End Function

Once you have that function installed, in your workbook, you can use a formula of:
=CreditDebit(C5)

Copy to the right 1 cell and down as needed. Formula will properly convert all the values.
 
Thanks Luke for your effort. I tried the above UDF however the result appears as #NAME?
Pls note that though the amount in the excel table appears with Dr. or Cr, however the autosum range consider all the numeric values as positive irrespective of Dr or Cr. Request you to see the attached excel table minutely.
 
Sounds like you didn't put the code in the correct spot.

As I mentioned, the Dr/Cr is only in the format of the cell; that's why you're still able to sum the values. See attached for example of proper location for code.
upload_2015-12-9_10-34-31.png
 

Attachments

  • TrialBal LM.xls
    36 KB · Views: 168
Hi


Here's a simple solution to your problem. The end goal is to convert all credits to negative numbers:


1. Open a new blank excel workbook and "Save as" -> .csv format (Comma delimited). A .csv file does not store formulae, formatting etc.


2. From your Tally-extracted excel file, copy the sheet with the Dr-Cr formatted columns and paste into the blank .csv file


3. Save and close the .csv file. Now reopen it.


4. You'll see that all the columns in the .csv file with Dr-Cr formats now include Dr and Cr suffixed as a part of the string itself. This is now easy to work with as it can be easily read by any normal excel formula. Formulae otherwise don’t read Cr/Dr when they’re part of the format itself.


5. Copy all the data back from your .csv into a new sheet (say, Sheet 2) in your excel.


6. In sheet 3 of the excel file, apply the following formula for the cells with data (here I'm using cell H5 as an example):

=IF(RIGHT('Sheet 2'!H5,2)="Cr",-'Sheet 1'!H5,'Sheet 1'!H5)


What does this do? From the Sheet 2 that contains your un-formatted data (with Dr/Cr along with the number as string), you are first reading whether that particular cell has "Cr" as the last 2 characters of the string. If it does, you are converting that cell to negative by specifying minus of the corresponding cell from Sheet 1. If there is no "Cr" present in the string, it simply returns the original value as it is from Sheet 1.

This is a pretty simple solution as it gives you the flexibility to do whatever you want with your data once you can see Dr/Cr in the cell value.
 
Hi,

Alternatively, you can simply apply filter >> search in Filter "Cr", >> Multiply these numbers with -1 (Apply for Visible Cells only).

Hope you get desired result.
 
Last edited by a moderator:
HI Guys,

Would someone be able to help me convert this file? From CR and Dr into a negative positive figure so I can add up the totals for each year
 

Attachments

  • Letter.xlsx
    89.3 KB · Views: 7
AzzA007
Please reread Forum Rules
especially How to get the Best Results at Chandoo.org
This thread is ... few years old and for other member.
Open a new thread for You.
 
Status
Not open for further replies.
Back
Top