Formula help needed

[mplees]

Hello all,

Its been over 3 years since I last did anything Excel related, so would really appreciate some help with constructing a formula.

The formula I currently have is:

=IF(C2="","",ROUNDUP(E2+(E2*65%),1))

Cell C2 contains a product description and cell E2 the buying price for the product. While the roundup part of the formula works fine, I would like to refine it so that if the rounded price is above £x.50 (£1.60, £6.70 etc), the result would be increased to the next whole £ (so in the examples they would round up to £2.00, £7.00, etc); if the rounded price is below £x.50 but above £x.20 then it would increase the value to £x.50, and finally in the remaining case round down to the whole £.

Hope that someone can both understand what I am asking, & point me in the right direction!

regards

Mark

 

[lenze]

Kind of ugly, but it seems to work!!

=IF(C2="","",IF(MOD(E2*1.65,1)<0.2,FLOOR(E2*1.65,1),IF(MOD(E2*1.65,1)<0.5,CEILING(E2*1.65,0.5),CEILING(E2*1.65,1))))

lenze

 

[barry houdini]

Hello Mark, try

=IF(C2="","",IF(MOD(E2*1.65,1)>0.2,CEILING(E2*1.65,0.5),INT(E2*1.65)))

 

[Andrew Poulsom]

An alternative:

=IF(C2="","",ROUND(E2*165%,0)+(0.5*AND(MOD(E2*165%,1)>=0.2,MOD(E2*165%,1)<0.5)))

 

[mplees]

Gentlemen,

Thank you so much for your assistance - it is greatly appreciated!

Regards

Mark