Home

Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-393500%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393500%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20984px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F101510i987EA1620BF2F19B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture3.PNG%22%20title%3D%22Capture3.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20the%20photo%20above%20im%20trying%20to%20create%20a%20formula%20in%20J3-J5.%26nbsp%3B%20a%20quick%20description.%26nbsp%3B%20Black%20pants%20cost%20%2450%20and%20Tan%20pants%20cost%20%2460.%26nbsp%3B%20but%20if%20the%20person%20needs%20XXX-Large%20pants%20there%20is%20an%20added%20%2410%20fee%20for%20anything%20above%20XXX-large%20or%20higher.%26nbsp%3BSo%20for%20STEVE%26nbsp%3Bi%20need%20the%20formula%20to%20calculate%20the%20Sum%20of%20E5%2BH5%20but%20i%20also%20need%20it%20to%20calculate%20an%20extra%26nbsp%3B%2410%26nbsp%3Bfor%20steve%20and%20anyone%20else%20that%20needs%20XXX-Large%20pants%20but%20at%20the%20same%20time%20not%20adding%20an%20extra%20%2410%20fee%20for%20matt%20who%20got%20a%20Medium%20size%20pant.%26nbsp%3B%20hopefully%20this%20isn't%20to%20confusing%20and%20any%20help%20would%20be%20greatly%20appreciated.%26nbsp%3B%20Thank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89704%22%20target%3D%22_blank%22%3E%40Damien%20Rosario%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-393500%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394361%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394361%22%20slang%3D%22en-US%22%3EUse%20SUM%20like%20this%3A%3CBR%20%2F%3E%3DSUM(ISNUMBER(SEARCH(%E2%80%9CXXX%E2%80%9D%2CI3))*10%2CE3%2CH3)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394359%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20984px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F101719i1A6E2636F770C972%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture6.PNG%22%20title%3D%22Capture6.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eok%26nbsp%3Bso%20I%20went%26nbsp%3Bback%20to%26nbsp%3Bthe%26nbsp%3Boriginal%20photo%20I%20posted%20and%20inputted%20that%20formula%20but%20is%20giving%20me%20the%20same%20error.%26nbsp%3B%20what%20am%20I%20doing%20wrong%20Sir%3F%26nbsp%3B%26nbsp%3B%20the%20formula%20works%20as%20long%20as%20someone%20gets%20a%20jersey%20as%20well.%26nbsp%3B%20again%20thank%20you%20for%20your%20time%20I%20really%20appreciate%20it.%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394346%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394346%22%20slang%3D%22en-US%22%3EYou%20modified%20the%20layout%20of%20your%20data%2C%20such%20that%20the%20formula%20in%20K3%2C%20copied%20down%20to%20K5%2C%20must%20also%20be%20modified%20as%3A%3CBR%20%2F%3E%3DE3%2BJ3%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394277%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F101704iA82CC80D107FEDF8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture4.PNG%22%20title%3D%22Capture4.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHi%20Sir%2C%20so%20as%20you%20can%20see%20when%20I%20put%20in%20that%20formula%20its%20giving%20me%20a%20%23value!%20error%20cause%20nothing%20is%20inputted%20in%20E3.%26nbsp%3B%20matt%20and%20steve%20don't%20need%20jerseys.%26nbsp%3B%20is%20there%20anyway%20to%20fix%20the%20formula%20to%20account%20for%20that%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393977%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393977%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393959%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393959%22%20slang%3D%22en-US%22%3E%3CP%3EThank%26nbsp%3Byou%26nbsp%3BSir.%26nbsp%3BYou%20are%20a%20genius%20haha.%26nbsp%3B%20%26nbsp%3BI%20really%20appreciate%20it!%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393523%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393523%22%20slang%3D%22en-US%22%3EThe%20formula%20you%20need%20in%20J3%2C%20copied%20down%20to%20J5%2C%20is%3A%3CBR%20%2F%3E%3DISNUMBER(SEARCH(%22XXX%22%2CI3))*10%2BE3%2BH3%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393510%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393510%22%20slang%3D%22en-US%22%3E%3CP%3EYes%26nbsp%3BSir%2C%26nbsp%3Bthere%20are%20small%2C%20medium%2C%20large%2C%20x-large%2C%20xx-large%2C%20xxx-large%20and%20xxxx-large.%26nbsp%3B%26nbsp%3B%20xxx-large%20and%20xxxx-large%20will%20require%20an%20extra%20%2410%20fee.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393509%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393509%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20are%20the%20possible%20sizes%20of%20Pants%3F%3CBR%20%2F%3EAside%20from%20%22XXX-Large%22%2C%20what%20are%20the%20other%20sizes%20that%20require%20extra%20%2410%20fee%3F%20Aside%20from%20%22Medium%22%20and%20%22Large%22%2C%20are%20there%20other%20sizes%20of%20that%20do%20not%20require%20extra%20%2410%20fee%3F%20For%20example%2C%20are%20there%20%22Small%22%20and%20%22XXXX-Large%22%20sizes%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-522902%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-522902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111894i081673FBABD2FBB5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20another%20question%20for%20you%20geniuses.%26nbsp%3B%20As%20you%20can%20see%20i%20have%20a%20master%20chart%20here%20and%20im%20trying%20to%20link%20a%20whole%20column%20to%20another%20worksheet%20based%20on%20the%20selection%20of%20what%20%22Engine%22%20they%20select.%20So%20at%20the%20bottom%20of%20this%20worksheet%20i%20have%203%20tabs%20labeled%20%22Engine%2012%22%2C%20%22Engine%2013%22%20and%20%22Engine%2014%22.%26nbsp%3B%20This%20sheet%20you%20see%20is%20the%20master%20sheet.%26nbsp%3B%20So%20when%20Joe%20Williams%20selects%20Engine%2012%20as%20the%20engine%20he%20will%20be%20working%20on%20i%20want%20the%20whole%20column%20of%20his%20info%20to%20link%20to%20the%20Engine%2012%20worksheet%20automatically.%26nbsp%3B%20I%20hope%20this%20makes%20sense%20to%20you%20all%20and%20please%20let%20me%20know%20if%20you%20need%20any%20other%20info.%26nbsp%3B%20thank%20you%20so%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89704%22%20target%3D%22_blank%22%3E%40Damien%20Rosario%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-522937%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-522937%22%20slang%3D%22en-US%22%3EGiven%20that%20you%20are%20raising%20another%20issue%2C%20I%20suggest%20you%20start%20another%20conversation%20and%20attach%20your%20sample%20file%20therein.%20Such%20file%20must%20contain%20your%20sample%20data%20along%20with%20the%20manually%20entered%20results%20which%20you%20want%20to%20be%20returned%20by%20formulas.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-522943%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-522943%22%20slang%3D%22en-US%22%3E%3CP%3Eok%20thank%20you%20sir%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
spalmer
Contributor

Capture3.PNG

In the photo above im trying to create a formula in J3-J5.  a quick description.  Black pants cost $50 and Tan pants cost $60.  but if the person needs XXX-Large pants there is an added $10 fee for anything above XXX-large or higher. So for STEVE i need the formula to calculate the Sum of E5+H5 but i also need it to calculate an extra $10 for steve and anyone else that needs XXX-Large pants but at the same time not adding an extra $10 fee for matt who got a Medium size pant.  hopefully this isn't to confusing and any help would be greatly appreciated.  Thank you

 

@Damien Rosario @Sergei Baklan @Twifoo 

12 Replies

What are the possible sizes of Pants?
Aside from "XXX-Large", what are the other sizes that require extra $10 fee? Aside from "Medium" and "Large", are there other sizes of that do not require extra $10 fee? For example, are there "Small" and "XXXX-Large" sizes?

Yes Sir, there are small, medium, large, x-large, xx-large, xxx-large and xxxx-large.   xxx-large and xxxx-large will require an extra $10 fee. @Twifoo 

The formula you need in J3, copied down to J5, is:
=ISNUMBER(SEARCH("XXX",I3))*10+E3+H3

Thank you Sir. You are a genius haha.   I really appreciate it!  @Twifoo 

You’re very much welcome!

Capture4.PNG

Hi Sir, so as you can see when I put in that formula its giving me a #value! error cause nothing is inputted in E3.  matt and steve don't need jerseys.  is there anyway to fix the formula to account for that?

@Twifoo 

You modified the layout of your data, such that the formula in K3, copied down to K5, must also be modified as:
=E3+J3

Capture6.PNG

ok so I went back to the original photo I posted and inputted that formula but is giving me the same error.  what am I doing wrong Sir?   the formula works as long as someone gets a jersey as well.  again thank you for your time I really appreciate it.  @Twifoo 

Use SUM like this:
=SUM(ISNUMBER(SEARCH(“XXX”,I3))*10,E3,H3)

Capture.JPG

 

I have another question for you geniuses.  As you can see i have a master chart here and im trying to link a whole column to another worksheet based on the selection of what "Engine" they select. So at the bottom of this worksheet i have 3 tabs labeled "Engine 12", "Engine 13" and "Engine 14".  This sheet you see is the master sheet.  So when Joe Williams selects Engine 12 as the engine he will be working on i want the whole column of his info to link to the Engine 12 worksheet automatically.  I hope this makes sense to you all and please let me know if you need any other info.  thank you so much @Twifoo @Damien Rosario @Sergei Baklan 

Given that you are raising another issue, I suggest you start another conversation and attach your sample file therein. Such file must contain your sample data along with the manually entered results which you want to be returned by formulas.

ok thank you sir@Twifoo 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies