Geek Answers Handbook

Use if statement for one of the decimal values ​​in Excel.

I need help creating a formula that rounds a number from 1 or 6 in places located to the nearest multiple of 5 (e.g., from 276 to 275 or 131 to 130) and rounds any other number to the nearest plural of 5 (e.g., from 277 to 280 or 132 to 135). I realized that the logic would look something like this:

= if (cannot determine this condition, ceiling (A1.5), floor (A1.5))

0
source share
2 answers

Instead, you can use MROUND:

=MROUND(A1,5)

5. , 277,5 , 280, , , 275.

IF, MOD:

=IF(MOD(A1,5)>2.5, CEILING(A1,5), FLOOR(A1,5))

, 2,5 5, , - .

0

- :

=IF(OR(RIGHT(A1,1)="1",RIGHT(A1,1)="6"),FLOOR(A1,5),CEILING(A1,5)))

:

=IF(ISNUMBER(FIND(RIGHT(A1,1),"16")),FLOOR(A1,5),CEILING(A1,5))
0

More articles:


All Articles