• 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 a positive and Dr as a negative

AzzA007

New Member
Hi Guys,

Can some one help me? I have tried to apply a formula to this without much success.Can someone help me??I just want to be able to total the amount. I have used this =IF(RIGHT(B2,2)="cr",LEFT(B2,LEN(B2)-2),CONCATENATE("-",LEFT(B2,LEN(B2)-2)))
 

Attachments

  • Letter.xlsx
    89.3 KB · Views: 8
Last edited:
can you use the amount columns F & G without the characters "DR" & "CR" in them?
if you can change them and column F write each amount with a minus sign, like -14029.95
and in column G write them as standard, like 14,029.95
then its just simple
if you must add the "DR" & "CR" then its a whole work around
 
just add "Value" in your formula and Bingo!
like this:
=Value(IF(RIGHT(B2,2)="cr",LEFT(B2,LEN(B2)-2),CONCATENATE("-",LEFT(B2,LEN(B2)-2))) )
 
HI GUYS,

Say if i use column G,I and K as a column for the figure without DR and CR what formula would i use? it doesnt work when i do it. Would you be able to post the formula like how it would type on the spread sheet plz? sorry guys I'm a Noob when it comes to this
 

Attachments

  • Letter (3).xlsx
    12.7 KB · Views: 6
Last edited:
=Value(IF(RIGHT(B2,2)="cr",LEFT(B2,LEN(B2)-2),CONCATENATE("-",LEFT(B2,LEN(B2)-2))) )

This the formula i used with no success. I typed exactly the above
 
Hi,

Use this for F column:
=IFERROR(SUBSTITUTE(F4,"DR","")*-1,"")

and this for Column G:
=IFERROR(SUBSTITUTE(G5,"CR","")*1,"")


or single formula for both columns:
=IFERROR(IF(ISNUMBER(SEARCH("dr",F4)),SUBSTITUTE(F4,"DR","")*-1,SUBSTITUTE(F4,"CR","")*1),"")
Regards,
 
Back
Top