Add values> 0 and <0 in one cell

[Berna11]

Good evening as the title suggests, I can't make the simulation of a column with formula to have a single total.

In the attached file you will find the example of column J to be added and the attempts made one at a time work but, if I combine the formula with the wrong result I40

Thanks

[Kaper]

Why not just:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The latter probably (/) shall be committed as array formula in Excel 2007 and 2010

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Berna11]

hanks
but, maybe I didn't explain myself
the sum of numbers> 0 = 64
the sum numbers <0 = -64
Making the sum must give result = 0

[Fluff13]

Why not just use SUM?
Or to get rid of the minute fractions something like
=ROUND(SUM(J6:J36),2)

[joeu2004]

maybe I didn't explain myself
the sum of numbers> 0 = 64
the sum numbers <0 = -64
Making the sum must give result = 0

Much better! In other words, the formula =SUM(J6:J36) is what you want, as you wrote in J37.

The only problem is: the result is not exactly zero.

You should write: =ROUND(SUM(J36:J36),0) if you expect an integer result.

Alternatively, you might correct the time arithmetic in each of J6:J36. Write: ROUND((L7-K7-M7)*24,0).

-----

The problem is: Excel time is represented as a fraction of 1 day; for example, 1 hour = 1/24. And most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which Excel uses to represent numerical values internally.

Consequently, what appears to be exact integers in J6:J36 are not(!). For example, note that =J7-INT(J7)=0 returns FALSE(!).

[Kaper]

Indeed, original formula was fine.
If you want just to see the result - use formatting of the cell - not general, but a number with desired (0?) decimal digits.
If you plan to use the result in further calculations - use rounding.

[Berna11]

ok, correct:
= ROUND (SUM (J6: J36), 0)

or:
= ROUND (SUM (J6: J36), 2)

thanks

thanks

[Fluff13]

You're welcome & thanks for the feedback