Round up issue

[brazill23]

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

 

[wrightyrx7]

You tried this?

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

 

[Dan5977]

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]

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]

Hi Guys,

Thank you very much all works perfect now