Results 1 to 6 of 6

Formula to calculate difference between 2 #'s

This is a discussion on Formula to calculate difference between 2 #'s within the Excel Questions forums, part of the Question Forums category; I am trying to get excel to calculate the difference between two cells. The fields contain $ amounts. Column A ...

  1. #1
    New Member
    Join Date
    Apr 2003
    Location
    FLORIDA
    Posts
    4

    Default Formula to calculate difference between 2 #'s

    I am trying to get excel to calculate the difference between two cells. The fields contain $ amounts. Column A is 2002 daily sales figures. Column B is 2003 daily sales fig's. I need excel to calculate the difference between those 2 figures giving me a $ amt. over or under last year, as well as percent over or under. I would like it to show me daily diff. also, running total difference if this is possible.

    Any help would be greatly appreciated.
    Thanks!!!!!!!!!!!
    Steven King
    Orlando Fl

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Toronto, Canada
    Posts
    248

    Default

    Assuming your numbers are in cell A1 and B1, in cell C1 you can enter

    =A1-B1 to get the difference between the 2 numbers. In Cell D1 enter

    =C1/A1 to get the percentage increase or decrease.

    I don't know if I am missing something because this seems almost too basic a question. Is this what you wanted or is there something else that I am not seeing?

    Neville.

  3. #3
    New Member
    Join Date
    Apr 2003
    Location
    FLORIDA
    Posts
    4

    Default

    I hope that will work...
    It is a very basic question as I don't use this program often.

    The only thing is that sometimes A1 is greater than B1 and sometimes B1 is greater than A1.

    Will this still work in both cases?

    Thanks!
    I can't wait to try this.

    Steven King
    Orlando Fl

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Toronto, Canada
    Posts
    248

    Default

    It will currently work, however you will get either a positive or negative result depending on which number is larger. If you always want to subtract the smaller number from the larger use the following:

    =IF(A1>B1,+A1-B1,B1-A1)

    This formula determines if A1 is larger than B1 and if this is true subtracts B1 from A1, if this is not true, the formula subtracts A1 from B1 - either way the result will be a positive number.

    I hope this helps.


    Neville.

  5. #5
    New Member
    Join Date
    Apr 2003
    Location
    FLORIDA
    Posts
    4

    Default

    You are the man... I just tried it and it worked...!

    Do I have to type this formula into each row? There are 31 rows in 6 columns. Or is there a way to format the column to do this on every line?

    Also, the value it returns dollar of percent is positive. Can it show a negative if 2003 is less than 2002?

    You have been very very helpful.

    Thanks,
    Steven King
    Orlando Fl

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Toronto, Canada
    Posts
    248

    Default

    You can copy the formula down to each row and it will refer to the other figures on a "relative" basis (i.e. a formula in cell C1, refers to A1 and B1, while a formula in cell C2, refers to A2 and B2). This is done with a simple edit, copy, (move to where you want the formula copied) and edit, paste. Or if you hold the mouse over the bottom-right corner of the cell that the formula is located in you will see a bold cross - once this appears you can "drag" the formula to as many cells as you want.

    As far as showing a negative percentage figure, go into Format, Cells, Number, Percentage and you can choose the number of decimals for the percentage shown, or if you want negatives in parentheses, select Format, Cells, Number, Custom and where the 0.00% shows in "type" you can simply add the parentheses you need.

    Give these tips a try - and don't be afraid to use the Excel help - there are many features in there that will help you.

    Neville.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com