Excel - Rounding a formula to 5?

[aprisa]

Ok so I'm thick when it comes to Excel formulae!

Can someone give me the formula to take a third off a number and then round it down to the nearest multiple of 5 to make it easier in monetary terms?

Thanks
Nick

PS I've looked at the MRound etc and get totally confused!

[Jay-Aim]

the formula is "ceiling"

[CanAm-TT]

=INT(INT((2*A2)/3)/5)*5

where A2 is the original number

[CanAm-TT]

The problem with celing is it rounds up, so you need to make it complicated by putting an if statement in there to check if the is the correct one or 5 over

[aprisa]

CanAm-TT said:

=INT(INT((2*A2)/3)/5)*5 where A2 is the original number

That's worked a treat, many thanks

Nick

[CanAm-TT]

aprisa said:

That's worked a treat, many thanks Nick

You're more than welcome

[buckett]

Or even easier:

=MROUND(B1,5) ... where B1 is the cell with the value that you want to round, and 5 is the multiple that you wish to round by. You have to have the Analysis ToolPak add-in installed.

[CanAm-TT]

Yes that is the best method, but not many people have the analysis toolpack loaded.