# Round up issue

#### brazill23

##### New Member
HI All,

I know it might sounds silly but Im looking to round up or down below formula. I was trying multiple ways and still excel not rounding up this

IF(K10="Order",((SUM(\$J5:\$U5))/(COUNT(\$J5:\$U5)-COUNTIF(\$J5:\$U5,0)))*X8,0)

result in this example was 935

Thank you for any ideas

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### wrightyrx7

##### Well-known Member
You tried this?

=ROUNDUP(IF(K10="Order",((SUM(\$J5:\$U5))/(COUNT(\$J5:\$U5)-COUNTIF(\$J5:\$U5,0)))*X8,0))

#### Dan5977

##### Board Regular
You tried this?

=ROUNDUP(IF(K10="Order",((SUM(\$J5:\$U5))/(COUNT(\$J5:\$U5)-COUNTIF(\$J5:\$U5,0)))*X8,0))

=ROUNDUP(IF(K10="Order",((SUM(\$J5:\$U5))/(COUNT(\$J5:\$U5)-COUNTIF(\$J5:\$U5,0)))*X8,0),0)

#### joeu2004

##### Well-known Member
Does "round up or down" mean round "half" or more up, otherwise round down?

If so, you want ROUND, not ROUNDUP.

IF(K10="Order", ROUND(X8*SUM(\$J5:\$U5)/(COUNT(\$J5:\$U5)-COUNTIF(\$J5:\$U5,0)), 0), 0)
or
IF(K10="Order", ROUND(X8*SUM(\$J5:\$U5)/COUNT(\$J5:\$U5,"<>0"),0), 0)
or
IF(K10="Order", ROUND(X8*AVERAGEIF(\$J5:\$U5,"<>0"),0), 0)

The last form requires Excel 2007 or later. And it would be prudent to write the following, in case all of the values are zero:

IF(K10="Order", IFERROR(ROUND(X8*AVERAGEIF(\$J5:\$U5,"<>0"),0), 0), 0)

If you truly mean always round up (not "or down"), replace ROUND with ROUNDUP.

#### brazill23

##### New Member
Hi Guys,

Thank you very much all works perfect now

Replies
2
Views
188
Replies
6
Views
132
Replies
6
Views
98
Replies
6
Views
107
Replies
1
Views
167

1,109,549
Messages
5,529,467
Members
409,883
Latest member
asharris90