View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JE McGimpsey JE McGimpsey is offline
external usenet poster
 
Posts: 4,624
Default Rounding numbers up or down in same formula

Assuming your values are integers, one way:

=CEILING(A1 - 2.0000000001, 5)



In article ,
Tired wrote:

A value ending in 0 through 2 needs to round down to 0 (e.g., $25,402 =
$25,400).

A value ending in 3 through 5, needs to round up to the nearest 5 (e.g.,
$24,174 = $24,175).

A value ending in 6 through 7, needs to round down to the nearest 5 (e.g.,
$18,217 - $18,215).

A value ending in 8 or 9, needs to round up the nearest 10 (e.g., $14,408 =
$14410)