Search code examples
excelexcel-formularounding

How to round value by specific value after decimal and change all value to zero after round

I use excel and i have question. I have value look like

7,500,300

If value after first comma less than 5(it 5 on my case) now round value into 0. If value after first comma greater than or equal 5 now value into 5

Example:

7,500,300 => 7,500,000
7,520,200 => 7,500,000
7,940,200 => 7,500,000
7,240,300 => 7,000,000

I using command look like :

=IF((VALUE(MID(7,940,200,FIND(",",7,940,200)+1,1))>=5),ROUND(7,940,200),ROUNDDOWN(7,940,200)

But it not working.

Please help me. Thanks you

Solution

  • Try this formula:

    =INT(A1/1000000)*1000000 + IF(MOD(A1,1000000)>=500000,500000,0)