[Solved] Formula to subtract A1-A2, with conditions

[donnje]

Hi everyone,
Can anyone tell me the formula to subtract A1-A2 if A1>A2 and add if B1<B2?
Thanks

[Alex1]

You also need to tell what these formulas should give in case the conditions aren't satisfied.

[morchat]

I understand it like this: =A1-A2*(A1<A2)+A2*(B1<B2)

[donnje]

thanks I solved with this: =SE(A1>A2; A2-A1; A2+A1)
but now I would like that if A1 is negative then A2-A1 otherwise A1+A2, how is this possible?

[karolus]

…
but now I would like that if A1 is negative then A2-A1 otherwise A1+A2, how is this possible?

Code: Select all

=A2+ABS(A1)

[donnje]

sorry I attach the test file, I would like that in column C it is calculated from the difference between the two times and that in case the result is negative as in row C4 it is indicated as -03:00

[morchat]

Remember that time is represented by the fractional part of a number (from 0.0 to 0.99999(9)), e.g., 0.34375 is 8:15, so time cannot be a negative number. To get a negative result, as you want, the result is presented as text, so such a result cannot be used for arithmetic.

[MrProgrammer]

so time cannot be a negative number

Read about duration in section 4. Times in cells in Ten concepts that every Calc user should know.

[Alex1]

For duration instead of clock time just format column C as [HH]:MM instead of HH:MM or whatever is used in Italian.

[donnje]

I was using the same formula but why does it show 21:21:00 in my C4 file and not -3?

[Alex1]

The formatting determines how the result is shown. Please follow the link provided by MrProgrammer.