+ Reply to Thread
Results 1 to 14 of 14

Changing minus figures to positive figures

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    18

    Changing minus figures to positive figures

    I am creating a profit and loss balance sheet, so i can change figures in the data entry area and gain the figures i need. I have used the complex IF statement which i think is correct. The trouble I am having is that the figure is a minus figure instead of a positive figure. Is there anyway i can revert this.

    If the company has a net profit of a minus then they will take an overdraft. If the net profit is less than £-5000 then an overdraft will be taken at 15% if the company has a net profit than is less than 0 but more than -5000 then they will only get charged 3%.

    The overdraft is in place, however how am i supposed to make the overdraft figure a positive instead of negative? Please see the attached document, the error lays in the yellow coloured cells.

    thanks
    Last edited by sammy011; 12-25-2012 at 01:33 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing minus figures to positive figures

    Hi Sammy and season's greetings to you

    If i understand you correctly, try using the abs() fumction...
    =IF(C38<=0,ABS(C38)*1.03,IF(C38>=-5000,ABS(C38)*1.03)*IF(C38>0,0,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Changing minus figures to positive figures

    Maybe

    =IF(C38>0,0,ABS(LOOKUP(ABS(C38),{0,5000},{1.03,1.15})*C38))
    HTH
    Regards, Jeff

  4. #4
    Registered User
    Join Date
    12-24-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Changing minus figures to positive figures

    Quote Originally Posted by FDibbins View Post
    Hi Sammy and season's greetings to you

    If i understand you correctly, try using the abs() fumction...
    =IF(C38<=0,ABS(C38)*1.03,IF(C38>=-5000,ABS(C38)*1.03)*IF(C38>0,0,0))
    Yes my friend you have done it!!!! I cant believe how quick you did it!!

    Thank you soo much!! (Yes I am stressing on Christmas)

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing minus figures to positive figures

    Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

    its xmas, dont sweat it, just ask us how

  6. #6
    Registered User
    Join Date
    12-24-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Changing minus figures to positive figures

    =IF(C38<0,ABS(C38)*1.03,IF(C38>=-5000,ABS(C38)*1.15)*IF(C38>0,0,0))

    this is the formula i used.

    Is this forumla correct which should say if the debt in C38 is less than £-5000 and an overdraft is taken we will get charged 3% but if we have a debt which is over £-5000 we will get charged 15%?
    this is a overdraft

    never done something as complex as this, dont want to mess with the wrong figures.

    (yes ill mark it as solved, this is just another issues that i want to clarify)

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing minus figures to positive figures

    shees, im sorry, i didnt really even look at the logic of your formula i just used what you had

    looking at the formula again, i would suggest swapping it around a bit. it is always best to start with the "outlying" refence 1st, so that is the 1st evaluated. doing it the way you hav it, it will 1st test to see if C38 is <0, well, even if C38 is -6000, it is still < 0, right? and that is where it will stop evaluating, so it will never get chance to see if C38 is <-5000 - does that make sense?

    =IF(C38<=-5000,ABS(C38)*1.15,IF(C38<=0,ABS(C38)*1.03,IF(C38>0,0,0)))

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Changing minus figures to positive figures

    hi sammy011, do you mean lesser or equals to -5000 or lesser than -5000? this is for lesser or equals to -5000 (15%)
    =ABS(IF(C38<=-5000,C38*1.15,IF(C38<0,C38*1.03,0)))

    otherwise, lesser than -5000:
    =ABS(IF(C38<-5000,C38*1.15,IF(C38<0,C38*1.03,0)))
    Last edited by benishiryo; 12-25-2012 at 10:38 AM. Reason: mistake found

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  9. #9
    Registered User
    Join Date
    12-24-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Changing minus figures to positive figures

    i guess i could use the less one. but not 100% im new to excel.

    an overdraft facility has been arranged with the bank for up to £5k. The of the overdraft 3% per month if it doesnt exceed £5k. if the company overdrawn more than £5k then the cost is 15% per month.

    so shall i include it??

    fdibbins- i think your probably right!! i should change that thanks again!
    Last edited by sammy011; 12-25-2012 at 01:34 PM.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing minus figures to positive figures

    based on your last post regarding 3% for less than 5000, the formula suggested will work for that. the 15% will only kick in at 5000 or less (keeping in mind that -5001 is less than -5000)

    edit: when in doubt, test lol. give it a try with manually entered values in C38, and check you are getting the expected answer. you can always ctrl Z (undo) or reload the file to get back to where you were

  11. #11
    Registered User
    Join Date
    12-24-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Changing minus figures to positive figures

    Honestly FDibbins you are a a very clever man!! You solved all my issues in a jiffy!! I tested them manually and got the answers that i need!!

    I have repped everyone in this thread!!

    the final formula used was- =IF(C38<=-5000,ABS(C38)*1.15,IF(C38<=0,ABS(C38)*1.03,IF(C38>0,0,0)))

    Merry Christmas everyone!!

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing minus figures to positive figures

    Happy to help sammy and thanks for the rep, always appreciated. have a great xmas

  13. #13
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Changing minus figures to positive figures

    Quote Originally Posted by sammy011 View Post
    Honestly FDibbins you are a a very clever man!! You solved all my issues in a jiffy!! I tested them manually and got the answers that i need!!

    I have repped everyone in this thread!!

    the final formula used was- =IF(C38<=-5000,ABS(C38)*1.15,IF(C38<=0,ABS(C38)*1.03,IF(C38>0,0,0)))

    Merry Christmas everyone!!
    Shorter version:

    =((C38<=-5000)*0.12+1.03)*(-MIN(C38,))

  14. #14
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Changing minus figures to positive figures

    Slightly shorter than my previous post #13

    =((C38<=-5000)*0.12+1.03)*-MIN(C38,)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1