Aug 29 2019 04:46 AM - edited Aug 29 2019 04:47 AM
Hi all,
I would like excell to complete a formula for pricing goods.
Could someone kindly advise if the following is possible in one cell's formula or would it need to be over several cells and how best to acheive it.
value of a cell *2, then round up to nearest 9, but if value = multiple of 100 then -1 instead
for example
cost price is £44.00 formula value should equal £89.00
or
cost price is £100.00 formula value should equal £99.00
Any help greatly received
Aug 29 2019 05:10 AM
Hi @McBee83
Please use below formula
=IF(MOD(100,E5)>1,ROUND((E5*2),9),(E5-1))
Sample file is also attached for your reference.
Let me know if it works for you or otherwise.
Aug 29 2019 05:21 AM
Thank you for your reply.
It works for the multiples of 100 but if the value is £44 it gives a completed formula value of £88 not £89?
Aug 29 2019 05:43 AM
SolutionReplace your existing formula with the following one and let me know if that works for both the scenarios....
=IF(MOD(100,E5)>1,ROUNDUP(E5*2,-1)-1,(E5-1))
Aug 29 2019 06:43 AM
Aug 29 2019 05:43 AM
SolutionReplace your existing formula with the following one and let me know if that works for both the scenarios....
=IF(MOD(100,E5)>1,ROUNDUP(E5*2,-1)-1,(E5-1))