+ Reply to Thread
Results 1 to 7 of 7

calculating formula with a minus figure

  1. #1
    Forum Contributor
    Join Date
    03-12-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    138

    calculating formula with a minus figure

    hi
    I am trying to develop a formula which will work out a total but calculates a minus figure correctly to work out a variance total ...see below for example

    qty 1150 qty req 630 carryover -600.5 variance

    this is a table format


    currently using

    variance=qty-(qty req + carryover) this does not give the correct answer

    any ideas guys

    Regards

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculating formula with a minus figure

    Hi,

    What 'right' answer do you expect? It might be advisable if you upload a small example workbook showing all permutations of the relative sizes of the numbers and the result you expect. i.e. with qty rec > qty, positive carry over if relevant, negative carry over value excluding sign > qty req, etc..
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    03-12-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    138

    Re: calculating formula with a minus figure

    thanks for reply

    not sure how to upload a workbook to here

    I want the formula to take the -600.5 to zero by using that qty "if that makes sense" so the answer for -600.5+630 would be 1230.5

    sorry if making more complicated than needs be

    Regards

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculating formula with a minus figure

    Hi,

    The FAQ section tell you how to upload a workbook.
    Does this help? If not you need to upload the workbook.

    A1 = -600.5
    A2= +630

    then
    -A1+A2 = 1230.5

  5. #5
    Forum Contributor
    Join Date
    03-12-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    138

    Re: calculating formula with a minus figure

    hi

    that works great thanks.

    A1= 1150
    B1= 630
    C1= -600.5

    so

    D1=A1-(-C1+B1) so D1 = -80.5

    How would I include this in a formula to calculate in column D if column C has variants of both + and - figures

    eg.

    A1= 1150
    A2= 1200
    B1= 630
    B2= 500
    C1= -600.5
    C2= 100

    so

    D1= -80.5
    D2= +600

    and so on

    Many Thanks


    Regards
    Last edited by shrubfact; 03-13-2014 at 12:37 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: calculating formula with a minus figure

    I would probably use the ABS() function in this one: D1=A1-sum(abs(b1),abs(c1)) then copy down. The ABS() [absolute value] function converts all numbers, whether positive or negative, to positive. Based on the limited examples you have given, that looks like what you are trying to do.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Contributor
    Join Date
    03-12-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    138

    Re: calculating formula with a minus figure

    Thanks very much MrShorty....problem solved.....love this forum

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Preventing a minus figure being displayed when linked to other formula
    By Illustris13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2013, 06:39 PM
  2. [SOLVED] I neeed help on changing a minus figure so it does not show a minus
    By Kirstg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-24-2012, 11:44 AM
  3. Calculating monthly sales figure required to make annual turnover figure
    By CatIsoSio Sky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-27-2010, 04:42 PM
  4. Replies: 4
    Last Post: 09-24-2007, 06:12 AM
  5. Replies: 10
    Last Post: 09-01-2006, 05:47 PM

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