I need to write a formula to us one Ceiling formula if a retail ends in 0-5 and a different Ceiling formula if a retail ends in 6-9.

snoopyfan

New Member
Joined
Jun 8, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I have a column of retails and I need to add 0.05 and then round that up to the next amount ending in 5. (Ex: $2.71 + .05 = $2.76, rounds to $2.85.)
I have been trying to use a Ceiling formula like
=Ceiling((AD5+0.05),0.05) but that only works on the retails ending in numbers 6 through 9. Any number ending in 0 through 5 ends up with a "rounded" total ending in 0 instead of a 5
I have to then enter this formula instead in the cells with incorrect results.
=Ceiling((AD5+0.10),0.05)
I would like to either find one formula that will give me the correct rounded result or can I use an "IF" formula to combine the two above?
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,616
Office Version
  1. 365
Platform
  1. Windows
Try rounding before adding

=CEILING(AD5,0.1)+0.05
 

snoopyfan

New Member
Joined
Jun 8, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Thank you so much Jason, I knew there had to be an easy answer!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,888
Messages
5,574,842
Members
412,620
Latest member
sharma7s
Top