ROUND with an IF exception

[GWC2803]

Hi there,

I have a sheet that totals pricing for a specific package by adding values from 3 different sections on my worksheet, and this is rounded to the nearest figure ending in 9 using the MROUND function as follows

Please Login or Register  to view this content.

This has always worked fine, but now I am required to apply an additional parameter whereby if the "Package Total" ends in 09, I need the value in the cell to be reduced by a further 10 to round it down to the next 99 value.

How can I modify my formula to allow for this exception for values ending in 09? I've attached an example file below (the values in D3, D5 and D6 would not meet the new requirements as they currently end in 09, so I need these to show as £1,299, £1,499 and £1,299 respectively).

Thanks in advance.

[Richard Buttrey]

Without a helper column then one way

Please Login or Register  to view this content.

[GWC2803]

Without a helper column then one way

Please Login or Register  to view this content.

Wonderful, thank you! That works perfectly for the purposes required - I had never used the RIGHT / LEFT function in Excel before, so it's great to learn of their usage

[Richard Buttrey]

Hi

Glad it's worked for you. Don't forget there's also the MID() string slicing function

Also note what's often referred to as the 'double unary' operator. i.e. the two minus signs in succession --

This is often used to convert numbers that appear as text into proper numbers. It's needed here since the RIGHT function returns a text string of the last two characters