I have a spread sheet which calcula 1

[Tonyjstone]

I have a spread sheet which calculates the sale prices of products. The sale prices are calculated on a basic cost plus margins plus tax, basis to make the PROPOSED SALES price.
I would like to have the PROPOSED SALES price automatically rounded up to the ACTUAL SALES price so that all prices are at 5p intervals.
EG: if the PROPOSED SALES price is £1.20 no action is taken,
if the PROPOSED SALES price is £1.21, £1.22, £1.23, £1.24, it is rounded up to £1.25
if the PROPOSED SALES price is £1.25 no action is taken,
if the PROPOSED SALES price is £1.26, £1.27, £1.28, £1.29, it is rounded up to £1.30

and so on....

The range of possible PROPOSED SALES prices may be from £1.20 to £1.85

I have tried ROUNDUP & ROUND functions but cannot achieve the result I seek, and the only solution I have found so far is a 'LOOKUP' .

Is there a way of using the 'in cell' functions to achieve my goal ?

Thanx
Tony

 

[joerd]

Seems to me you need something like
=ROUNDUP(A1*20,0)/20
Regards,

Joerd

 

[BlackburnKL]

While a little complex, the following formula works:

=INT(A1) + IF(A1-INT(A1)>0,IF(A1-INT(A1)>0.5,1,0.5),0)

You will, of course, need to replace A1 with the appropriate cell ID. BlackburnKL

 

[Tonyjstone]

Thanx joerd,
I think my mind is on holiday today. Your solution worked admirably.


BlackburnKL,

Thanx for taking the time and trouble.

Tony