# Using ROUND instead of MROUND

#### Eslava

##### Board Regular
I am sick and tired of MROUND not working whenever I send a workbook over to someone else. How do I substitute the ROUND function instead of the MROUND function for the following formula:

{=IF(SUM(IF(m_code=\$A9,weighted))=0,"--",MROUND(SUMPRODUCT(IF(m_code=\$A9,m_base25),(IF(m_code=\$A9,weighted)))/SUM(IF(m_code=\$A9,weighted)),(IF(SUMPRODUCT(IF(m_code=\$A9,m_base25),(IF(m_code=\$A9,weighted)))/SUM(IF(m_code=\$A9,weighted))>=100,1,0.5))))}

the formula works but I just want to use ROUND instead of MROUND...

Thanks!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Scott Huish

##### MrExcel MVP
Not quite following that formula, what are you looking to round the number to?

#### Eslava

##### Board Regular
I came up with this formula:

{=IF(SUM(IF(m_code=\$A9,weighted))=0,"--",IF(SUMPRODUCT(IF(m_code=\$A9,m_base25),(IF(m_code=\$A9,weighted)))/SUM(IF(m_code=\$A9,weighted))>=100,ROUND(SUMPRODUCT(IF(m_code=\$A9,m_base25),(IF(m_code=\$A9,weighted)))/SUM(IF(m_code=\$A9,weighted))/1,0)*1,ROUND(SUMPRODUCT(IF(m_code=\$A9,m_base25),(IF(m_code=\$A9,weighted)))/SUM(IF(m_code=\$A9,weighted))/0.5,0)*0.5))}

It seems to work. Basically I am trying to round to the nearest 1 if the number is 100 or over and to the nearest .5 if it is under 100.

#### Scott Huish

##### MrExcel MVP
Replace A1 with what you are looking to round:

=IF(A1<100,ROUND(A1*2,0)/2,ROUND(A1,0))

#### Scott Huish

##### MrExcel MVP
That formula could probably be simplified, for example why the IFs in the SUMPRODUCT?

=SUMPRODUCT(--(m_code=\$A\$9),m_base25,weighted)

Why divide by 1 here and why not SUMIF and why multiply by 1?

SUM(IF(m_code=\$A9,weighted))/1,0)*1
could be
=SUMIF(m_code,\$A9,weighted)

#### barry houdini

##### MrExcel MVP
Try

=IF(SUMPRODUCT(--(m_code=\$A\$9),weighted),INDEX(ROUND((SUMPRODUCT(--(m_code=\$A\$9),m_base25,weighted)/SUMPRODUCT(--(m_code=\$A\$9),weighted))*{1,2},0)/{1,2},1+(SUMPRODUCT(--(m_code=\$A\$9),m_base25,weighted)/SUMPRODUCT(--(m_code=\$A\$9),weighted)<100)),"--")

#### barry houdini

##### MrExcel MVP
As Hotpepper points out, the shorter SUMPRODUCT could be SUMIF so even better........

=IF(SUMIF(m_code,\$A\$9,weighted),INDEX(ROUND((SUMPRODUCT(--(m_code=\$A\$9),m_base25,weighted)/SUMIF(m_code,\$A\$9,weighted))*{1,2},0)/{1,2},1+(SUMPRODUCT(--(m_code=\$A\$9),m_base25,weighted)/SUMIF(m_code,\$A\$9,weighted)<100)),"--")

Replies
6
Views
361
Replies
3
Views
1K
Replies
0
Views
528
Replies
5
Views
1K
Replies
4
Views
2K

1,190,953
Messages
5,983,819
Members
439,862
Latest member
FaisalAlTawil

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back