How to Stop Rounding Errors?

**funwithcolors** · 09-21-2014, 05:36 PM

Hi again, so for my excel sheet I need to do three sets of calculations that require me to use ROUNDUP and ROUNDDOWN for 1000 calculations, and I keep getting that array error and I'm not really sure what to do :/ got my precision set and everything. Maybe the problem is I'm doing something like =ROUNDDOWN(F2:F1000*G2:G1000,0)?

For the ROUNDDOWN, I need to get my numbers to have decimal places at .00 (so instead of 101.01 it would be rounded to 101.00)

etc etc

**FDibbins** · 09-21-2014, 05:54 PM

rounddown works on a cell, not an array (as far as I know)

Perhaps you need something like this...

=ROUNDDOWN(sumproduct((F2:F1000)*G2:G1000)),0)

**TMS** · 09-21-2014, 05:59 PM

Or, conversely:

Formula:

Please Login or Register  to view this content.

And I think it needs a few less brackets.

Formula:

Please Login or Register  to view this content.

Note that the first does the rounddown in pairs and adds up whereas the second does the rounddown on the total

Regards, TMS

**FDibbins** · 09-21-2014, 07:08 PM

Originally Posted by TMS

And I think it needs a few less brackets.
Regards, TMS

True, I add them out of (bad) habbit lol...and actually, there was 1 missing...
=ROUNDDOWN(sumproduct((F2:F1000)*(G2:G1000)),0)

**TMS** · 09-21-2014, 07:34 PM

... and actually, there was 1 missing...

I know

To be honest, I usually put the extra brackets in because it helps me to see the ranges and how they are manipulated better. My bad habit is using a double negative where I don't always need it. It looks prettier

Regards, TMS

**joeu2004** · 09-21-2014, 07:54 PM

Originally Posted by FDibbins

True, I add them out of (bad) habbit lol...and actually, there was 1 missing... =ROUNDDOWN(sumproduct((F2:F1000)*(G2:G1000)),0)

Or simply:

=ROUNDDOWN(SUMPRODUCT(F2:F1000,G2:G1000),0)
or
=INT(SUMPRODUCT(F2:F1000,G2:G1000))

@funwithcolors: Compare ROUNDDOWN(-1.1,0) and INT(-1.1) to see which you want, if that is even relevant.

**FDibbins** · 09-21-2014, 08:54 PM

If no decimal places are required (as it seems) then using INT() is indeed a good option to use

**funwithcolors** · 09-22-2014, 12:14 AM

Okay, I got it. Thanks! but another q: what about for if I want only certain numbers to round up or down for tons of data?

**FDibbins** · 09-22-2014, 12:20 AM

hard to say without seeing samples of what you are working with

Are those numbers the result of input or formula?

**funwithcolors** · 09-22-2014, 12:28 AM

Halfway positive I attached a screenshot of my sheetbook onto this post. I'm pretty much using the results from column F (F2:f1000) [which were obtained through a formula] to get the highlighted row K results (which is decimals rounded to the fifth point [.00000] that are rounded up or down based on the number after the decimal)