=A2-A1 if answer is negative =A1-A2

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
Hi,
Can anyone help with this?
I would like to put a formulae in a cell that calculates A2-A1 but if the answer returned is negative to calculate A1-A2.

Can this be done??

Thanks in advance
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452

ADVERTISEMENT

Learnt another formulae...

Thanks
 

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
Thanks all.

The only problem is is that I am subtracting one date from another than adding one onto the answer.

E.g if the date is 10/10/06 and the other date is 10/10/06 the answer would be 1.

So the formulae I use is =(A1-A2)+1 and if negative I want =(A2-A1)+1.

=ABS(A1-A2)+1 doesnt return the same value as (A2-A1)+1.

Does anyone know a way around this??

Thanks
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374

ADVERTISEMENT

=ABS(A1-A2+1)
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
=ABS(A1-A2)+1 doesnt return the same value as (A2-A1)+1.

... yes it does, when A2 is larger than A1. Give an example of when you think this wouldn't be the case.
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
... yes it does, when A2 is larger than A1.

I believe the original reason for the post was because A2 is not always the larger number, and he needs one forumula for both situations.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
If A2 is not the larger number then (A2-A1)+1 would produce an incorrect answer. The formula provided copes with both situations, as far as I can see. I want an example of when:
=ABS(A1-A2)+1
doesn't do what is required.
 

Forum statistics

Threads
1,137,329
Messages
5,680,853
Members
419,935
Latest member
analyst0503

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top