Formula to calculate difference between 2 #'s

[STEVEKING]

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

 

[nevillestoke]

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.

 

[STEVEKING]

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

 

[nevillestoke]

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.

 

[STEVEKING]

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

 

[nevillestoke]

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.

 

[jamiehannon]

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.

This seems to be working for me, but not for -ve values.

I track stock in a bar and I want to record
The difference between what the computer says and the actual count.
Plus, whether that value has gone up or down from the previous day/count.

So, computer vs. actual for Beer may be -2 (i.e. two bottles are missing). The next day computer vs. actual is -4 (i.e. another two have gone missing), but i want my formula to show -2 not 2 i.e. I have lost - or gained - two bottles since that last count.

Thanks for the help.