SOLVED
Home

Excell formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-828880%22%20slang%3D%22en-US%22%3EExcell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828880%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20would%20like%20excell%20to%20complete%20a%20formula%20for%20pricing%20goods.%3C%2FP%3E%3CP%3ECould%20someone%20kindly%20advise%20if%20the%20following%20is%20possible%20in%20one%20cell's%20formula%20or%20would%20it%20need%20to%20be%20over%20several%20cells%20and%20how%20best%20to%20acheive%20it.%3C%2FP%3E%3CP%3Evalue%20of%20a%20cell%20*2%2C%26nbsp%3B%20then%20round%20up%20to%20nearest%209%2C%26nbsp%3B%20%26nbsp%3Bbut%20if%20value%20%3D%20multiple%20of%20100%20then%20-1%20instead%3C%2FP%3E%3CP%3Efor%20example%3C%2FP%3E%3CP%3Ecost%20price%20is%20%C2%A344.00%20formula%20value%20should%20equal%20%C2%A389.00%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3Ecost%20price%20is%20%C2%A3100.00%20formula%20value%20should%20equal%20%C2%A399.00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20greatly%20received%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-828880%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-828942%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828942%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400317%22%20target%3D%22_blank%22%3E%40McBee83%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20use%20below%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(MOD(100%2CE5)%26gt%3B1%2CROUND((E5*2)%2C9)%2C(E5-1))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20file%20is%20also%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20it%20works%20for%20you%20or%20otherwise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-828964%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3EIt%20works%20for%20the%20multiples%20of%20100%20but%20if%20the%20value%20is%20%C2%A344%20it%20gives%20a%20completed%20formula%20value%20of%20%C2%A388%20not%20%C2%A389%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-829001%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-829001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400317%22%20target%3D%22_blank%22%3E%40McBee83%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReplace%20your%20existing%20formula%20with%20the%20following%20one%20and%20let%20me%20know%20if%20that%20works%20for%20both%20the%20scenarios....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(MOD(100%2CE5)%26gt%3B1%2CROUNDUP(E5*2%2C-1)-1%2C(E5-1))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-829124%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-829124%22%20slang%3D%22en-US%22%3EYou%20sir%20are%20a%20genius%2C%20thank%20you%20so%20much.%20That%20worked%20a%20treat!%3C%2FLINGO-BODY%3E
McBee83
New Contributor

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

 

4 Replies

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.

 

@tauqeeracma 

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?

Solution

@McBee83 

Replace 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))

You sir are a genius, thank you so much. That worked a treat!
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies