Using ROUND instead of MROUND

[Eslava]

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!

 

[Scott Huish]

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

 

[Eslava]

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]

Replace A1 with what you are looking to round:

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

 

[Scott Huish]

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]

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]

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)),"--")