What is the formula to round up or down to the nearest "5"

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Need a formula for the following:

If [gal to order] is between 17 & 22, round to 20
If [gal to order] is between 22.01 & 27, round to 25

etc...
Photo of Lynne8817

Lynne8817

  • 530 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
I think your math is wrong. If you want to round to the nearest 5, Round(17,5) = 15 not 20.
Photo of Lynne8817

Lynne8817

  • 530 Points 500 badge 2x thumb
No math involved. I want all numbers between 17 & 22 to round to the number 20. Some will round up, some will round down. Also, 22.01 through 27 would round to 25...so on and so forth. Thx
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
I can't image a world without Math.

Numb3rs Scene: Everything is numbers, Math is everywhere
https://www.youtube.com/watch?v=vFRTgr7MfWw

If(
  [gal to order] >=17 and [gal to order] <22, 20,
  [gal to order] >=22 and [gal to order] <27, 25
)

An amazing story about an amazing mathematician:

Srinivasa Ramanujan
https://www.youtube.com/watch?v=Y7Ufxl-zSRI
Photo of Phil Pinkham

Phil Pinkham

  • 120 Points 100 badge 2x thumb
Round([gal to order]/5)*5

To prevent returning a zero use with an if statement to handle values <2.5

This will work with any desired multiple.