Round up issue

brazill23

New Member
Joined
Dec 18, 2018
Messages
11
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
 

Some videos you may like

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
Joined
Sep 15, 2011
Messages
994
You tried this?

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

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,813
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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