ROUNDUP issue

[Anar]

Hello,

I have 2 numbers 5.0 and 4.7
I take an average of these numbers and then round the result with both ROUND and ROUNDUP formulas. I get 4.9 in both cases.

I also have a table with thresholds. I want to look up 4.9 in this table. I used VLOOKUP to lookup 4.9.
In the case when I used ROUND I get 47.5%
But for ROUNDUP case I have #N/A.
Could someone please help why I get error with ROUNDUP.

4​	25.0%​
4.1​	27.5%​
4.3​	32.5%​
4.4​	35.0%​
4.5​	37.5%​
4.6​	40.0%​
4.7​	42.5%​
4.8​	45.0%​
4.9​	47.5%​
5​	50.0%​

 

[Peter_SSs]

It is probably an Excel floating point calculation error. See here: Floating-point arithmetic may give inaccurate result in Excel - Office

If your vlookup formula was, say
=VLOOKUP(B2,D2:E11,2,0)
the try this instead
=VLOOKUP(ROUND(B2,1),D2:E11,2,0)

 

[Alex Blakenburg]

I have 2 numbers 5.0 and 4.7
I take an average of these numbers and then round the result with both ROUND and ROUNDUP formulas. I get 4.9 in both cases.

To address the other point, the average of 5 & 4.7 is 4.85, you would expect that Round to 1 decimal place would round that up.

 

[Anar]

Thank you very much @Peter_SSs for the solution with =VLOOKUP(ROUND(B2,1),D2:E11,2,0). It helped a lot.
Still, though, I have a question that why ROUND returns a value, ROUNDUP does not.

 

[Peter_SSs]

Still, though, I have a question that why ROUND returns a value, ROUNDUP does not.

Did you read the article I linked to?
ROUND and ROUNDUP are obviously different functions and I don't know the programming behind how they work in Excel but presumably, on occasions, the different calculation methods may lead to very marginally different results.

 

[Anar]

Did you read the article I linked to?
ROUND and ROUNDUP are obviously different functions and I don't know the programming behind how they work in Excel but presumably, on occasions, the different calculation methods may lead to very marginally different results.

I read the article. Thank you for your help.