| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difference
I am sure there is a very simple answer to this.
I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
Hi Steve
Why not just =A1-B1 ? -- Regards Roger Govier "Stevep-4" wrote in message ... I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
Maybe a slight tweak
=ABS(A1-B1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Steve Why not just =A1-B1 ? -- Regards Roger Govier "Stevep-4" wrote in message ... I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
Thanks Bob
Hadn't noticed the OP wanted 245 and not -245 as his result. -- Regards Roger Govier "Bob Phillips" wrote in message ... Maybe a slight tweak =ABS(A1-B1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Steve Why not just =A1-B1 ? -- Regards Roger Govier "Stevep-4" wrote in message ... I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
=B1-A1 gives 245 with your example numbers, Excel knows elementary maths, why
didn't you try it? Regards, Stefi €˛Stevep-4€¯ ezt Ć*rta: I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
set up cell 'C' with =if(AB,A,B) and set up cell 'D' with =if(A<B,A,B)
then in cell 'E' have =C-D, which is the difference between the values. ensuring that you have the larger value in cell 'C' will stop you from achieving a negative value for the difference. "Stevep-4" wrote: I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
Thanks Hot dogs, that works. The other suggestions do not work due to the
fact that either cells A or B can be negative. Thus If A= -45 and B= 45. Difference is 90 If A= 45 and B= -45. Difference is 90. Is there a simpler method though, that can be done in one cell? for example =DIFF(A:B) "hot dogs" wrote: set up cell 'C' with =if(AB,A,B) and set up cell 'D' with =if(A<B,A,B) then in cell 'E' have =C-D, which is the difference between the values. ensuring that you have the larger value in cell 'C' will stop you from achieving a negative value for the difference. "Stevep-4" wrote: I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
See my amendment to Roger's response.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stevep-4" wrote in message ... Thanks Hot dogs, that works. The other suggestions do not work due to the fact that either cells A or B can be negative. Thus If A= -45 and B= 45. Difference is 90 If A= 45 and B= -45. Difference is 90. Is there a simpler method though, that can be done in one cell? for example =DIFF(A:B) "hot dogs" wrote: set up cell 'C' with =if(AB,A,B) and set up cell 'D' with =if(A<B,A,B) then in cell 'E' have =C-D, which is the difference between the values. ensuring that you have the larger value in cell 'C' will stop you from achieving a negative value for the difference. "Stevep-4" wrote: I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
What's wrong with =ABS(A1-B1)? (or =ABS(B1-A1)?)
Doesn't that give 90 for each of your examples? -- David Biddulph "Stevep-4" wrote in message ... Thanks Hot dogs, that works. The other suggestions do not work due to the fact that either cells A or B can be negative. Thus If A= -45 and B= 45. Difference is 90 If A= 45 and B= -45. Difference is 90. Is there a simpler method though, that can be done in one cell? for example =DIFF(A:B) "hot dogs" wrote: set up cell 'C' with =if(AB,A,B) and set up cell 'D' with =if(A<B,A,B) then in cell 'E' have =C-D, which is the difference between the values. ensuring that you have the larger value in cell 'C' will stop you from achieving a negative value for the difference. "Stevep-4" wrote: I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
to produce the result using one cell use
=if((A-B)<=0,((A-B)+(2*(0-(A-B)))),(A-B)) "Stevep-4" wrote: I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
"Stevep-4" wrote in message
... I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. =B1-A1 or =ABS(B1-A1) depending on what you want. -- David Biddulph |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Difference
ABS(B1-A1) works perfectly whether A or B (or both) are negative.
Thanks. "David Biddulph" wrote: "Stevep-4" wrote in message ... I am sure there is a very simple answer to this. I need to calulate the difference between 2 cells (each cell is a SUM of the information of other cells). the content of each of these cells could be +ve or -ve. Thus, if A is -45 and B is 200, then the difference is 245. Is there a simple was of doing this, rather than a long winded IF IF IF command. =B1-A1 or =ABS(B1-A1) depending on what you want. -- David Biddulph |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Averaging Time Difference | Excel Discussion (Misc queries) | |||
| Formula to find the working days difference between to dates? | Excel Discussion (Misc queries) | |||
| Difference between two months | Excel Worksheet Functions | |||
| Percentage difference calc that knows the largest figure | Excel Discussion (Misc queries) | |||
| charting a difference of 2 columns' w/o adding a difference column | Charts and Charting in Excel | |||
Hybrid Mode
