CODEWITHSUNDEEP
excel
Jannik
Jannik

Reputation: 2429

Round values before SUM()

Our customers use formulas in their input fields. Thats why their values sometimes contain decimal places. We can't round each field, cause it would be overrided by their formulas and input.

I have a field, which requires numbers with no decimal places. Is there a possibility to SUM() the ROUNDED values, something like a ROUND() function that uses a range of inputs?

Upvotes: 6

Views: 62683

Answers (5)

Kenny Long
Kenny Long

Reputation: 1

Providing the round function before sum would result in an incorrect entry.

Example: Assume two cells B2 & B3 are value 1.5

Using

=sum(round(b2:b3,0))

...the returned result would be 4. Both cells are rounded to 2 and then added. But using

=round(sum(b2:b3),0)

...the result would be 3. Both cells are added and the result rounded.

Upvotes: 0

mahesh patel
mahesh patel

Reputation: 1

Providing the sum function before round would cause the process to be slow and at times result in error. The best method would be

=round(sum(b2:b6),0))

Upvotes: 0

Michael E.
Michael E.

Reputation: 11

When I tried above I got an error. Resolved by putting the round first. =ROUND(SUM(B2:B6),0)

I used this with the following to convert then whole number (currency) to words. =SpellNumber(B7) where B7 is the cell with the previous formula.

Upvotes: 0

pnuts
pnuts

Reputation: 59495

I think what you may want is something like this:

=SUM(ROUND(A1:C1,0))

entered with Ctrl+Shift+Enter. The 0 is for the extent of rounding (no decimal places) but can be increased. So with data in each of A1:C1 (the range may be increased) of 12.4 the result is 36, with data of 12.6 the result is 39.

Upvotes: 9

Utsav Dawn
Utsav Dawn

Reputation: 8256

The solution to your problem is a combination of using both the SUM and the ROUND functions together. The effect is that you will get sum of rounded numbers. Here is how it looks:

Suppose we want to add the numbers from B2 to B6, you should do: {=SUM(ROUND(B2:B6,0))}

Let's look at the parts:

ROUND(B2:B6,0)- We will round each of the numbers to a whole number when we do the sum. 0 indicates no decimal points.

=SUM(ROUND(B2:B6,0))- We put a Sum around it to add the rounded numbers

{=SUM(ROUND(B2:B6,0))}- The finishing touch- important- we save the formula by clicking the Ctrl, Shift and Enter keys at the same time, since this is an array. This puts the brackets around the formula. If we just finished the formula by clicking Enter, we would get an error.

Upvotes: 4

Related Questions