Sum and round

  • Thread starter Thread starter crossy75
  • Start date Start date
C

crossy75

I have a column of 10 numbers each with 4.4 in them

I want to add the total to be 40 not 44 by rounding each component -
without going into each cell and using the round equation - is there a
Sumround function or way of doing this/

regards

Simon
 
This formula must be **array-entered**:

=SUM(ROUND(A1:A10,0))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>.  If you did this
correctly, Excel will place braces {...} around the formula.

=SUMPRODUCT(ROUND(A1:A10,0))

is also an array formula that will work, but you can enter this normally (just <enter> and not have to hold down the <ctrl><shift>; no braces will appear).
Click to expand...

Many thanks, the second one is much better it ensures other users can
manage it without messing it up!

much appreciated

Simon
 
=SUMPRODUCT(ROUND(A1:A10,0))
is also an array formula that will work, but you can
enter this normally (just <enter>
Click to expand...

Nitpick.... Excel reserves the term "array formula" for only formulas
that are entered by pressing ctrl+shift+Enter. Excel does not make a
distinction between "array-entered" and "array formula"; the terms are
synonyms.

Some quotes from the Excel Help....

"You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula."

"When you enter an array formula, Microsoft Excel automatically
inserts the formula between { } (braces)."

-----

Since both SUM and SUMPRODUCT accept array arguments, it has never
been clear to me why SUM(ROUND(A1:A10,0)) must be array-entered,
whereas SUMPRODUCT(ROUND(A1:A10,0)).

Also, since both IF and ROUND do not accept array arguments, it has
never been clear to me why SUMPRODUCT(IF(A1:A10>0,A1:A10)) must be
array-entered to work as intended [1], whereas
SUMPRODUCT(ROUND(A1:A10,0)) does not.

And since both ROUND and N do not accept array arguments, it has never
been clear to me why SUM(ROUND(A1:A10,0)) works as intended [1] when
it is array-entered, but SUM(N(A1:A10)) does not. And why
SUMPRODUCT(ROUND(A1:A10,0)) works as intended [1] when array-entered
or entered normally, but SUMPRODUCT(N(A1:A10)) does not.
 
And since both ROUND and N do not accept array arguments,
it has never been clear to me why SUM(ROUND(A1:A10,0))
works as intended [1] when it is array-entered, but
SUM(N(A1:A10)) does not.
Click to expand...

Well, I can make an educated guess.... Although the N function
argument is __defined__ as single-valued, perhaps the N function
argument is actually __implemented__ so that it accepts array
arguments (the equivalent of VBA Variant type), much like AND, which
also does not work as intended in an array formula like
SUM(IF(AND(A1:A10>0,B1:B10>0),C1:C10)).
 
Back
Top