SOLVED
Home

Excel formula Switch help

%3CLINGO-SUB%20id%3D%22lingo-sub-357570%22%20slang%3D%22en-US%22%3EExcel%20formula%20Switch%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357570%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20There%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20an%20issue%20with%20this%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSWITCH(W404%3DZ%242%2CTRUE%2CS404*0.1%2CFALSE%2CS404*0.025)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDespite%20the%20cell%20in%20question%20matching%20the%20fixed%20cell%20Z%242%20(being%20TRUE)%20the%20formula%20is%20calculating%20it%20as%20false%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20formula%20look%20incorrect%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-357570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360512%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Switch%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360512%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20for%20this%20Sergei%2C%20you're%20a%20gent!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358285%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Switch%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358285%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jim%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20data%20is%20not%20synced.%20For%20example%2C%20in%20some%20places%20you%20have%20%22Same%20Day%22%2C%20in%20others%20%22Same%20Day%20%22%20with%20the%20space%20at%20the%20end.%20You%20compare%20text%20%22TRUE%22%20with%20logical%20value%20TRUE%2C%20etc.%20I%20tried%20to%20eliminate%20with%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(ProbMatrix%5BCommission%20Rate%5D%2CMATCH(1%2C%20INDEX(%20(TRIM(%5B%40%5BAC%20Type%5D%5D)%3DProbMatrix%5BAccount%5D)%20*%20(%5B%40%5BSales%20Person%5D%5D%3DProbMatrix%5BSales%20Person%5D)%20*%20(%20(%5B%40%5BOver%20a%20Year%5D%5D%3D%22TRUE%22)%3DProbMatrix%5BOver%20a%20Year%5D)%2C0)%2C0))%3C%2FPRE%3E%0A%3CP%3EFor%20your%20helper%20table%20it's%20better%20to%20add%20one%20more%20column%20which%20says%20we%20have%20commission%20as%20per%20cent%20or%20as%20absolute%20value%2C%20when%20we%20may%20calculate%20it%20as%3C%2FP%3E%0A%3CPRE%3E%3DIF(INDEX(ProbMatrix%5B%25%5D%2CMATCH(1%2C%20INDEX(%20(TRIM(%5B%40%5BAC%20Type%5D%5D)%3DProbMatrix%5BAccount%5D)%20*%20(%5B%40%5BSales%20Person%5D%5D%3DProbMatrix%5BSales%20Person%5D)%20*%20(%20(%5B%40%5BOver%20a%20Year%5D%5D%3D%22TRUE%22)%3DProbMatrix%5BOver%20a%20Year%5D)%2C0)%2C0))%3D%22Y%22%2C%5B%40%5BCourier%20Pay%5D%5D*%5B%40Commission%5D%2C%5B%40Commission%5D)%3C%2FPRE%3E%0A%3CP%3EI'm%20not%20sure%20where%20is%20the%20formula%20from%20which%20you%20started%20this%20conversation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358206%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Switch%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358206%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20this.%26nbsp%3B%20I%20have%20attached%20Aprils%20sheet%20I%20am%20trying%20to%20get%20to%20work%20and%20removed%20any%20personal%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sheet%20works%20by%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20D%20-%20User%20Selects%20an%20account%20from%20drop%20down%20list(%20from%20Tab%20Accounts)%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20O%20-%20User%20Inputs%20Charge%20-%20Sheets%20works%20out%20VAT%20%26amp%3B%20Total%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20R%20-%20User%20Enters%20Cost%20-%20Sheet%20then%20works%20out%20the%20difference%20in%20Column%20T%20%26amp%3B%20Margin%20in%20U%26nbsp%3B%3C%2FP%3E%3CP%3EColumns%20V%2C%20W%20%26amp%3B%20X%20are%20pulled%20also%20from%20Tab%20Accounts%20based%20on%20different%20account%20types%2C%20salesman%20%26amp%3B%20whether%20the%20account%20is%20a%20year%20old.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20all%20works%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20Y%20-%20What%20I%20am%20trying%20to%20do%20is%20to%20work%20out%20a%20formula%20that%20can%20look%20at%20all%20the%20possible%26nbsp%3Bcombinations%26nbsp%3Bof%20these%20and%20return%20either%20a%20%25%20or%20a%20set%20%C2%A3%20amount%20(listed%20in%20column%20AF)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20Column%20Z%20needs%20to%20look%20at%20Column%20Y%20and%20multiply%26nbsp%3Bby%20the%20%25%20or%20just%20add%20the%26nbsp%3B%C2%A3%20amount%20if%20the%20combination%26nbsp%3Bof%20words%20from%20VWX%20are%20Route%20Cover%2C%20Reint%2C%20False%20(over%20a%20year%20old)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20I'm%26nbsp%3Bmaking%20it%20too%20complicated%26nbsp%3Band%20I%20don't%26nbsp%3Bneed%20the%20table%20on%20the%20right%3F%26nbsp%3B%20I%20was%20hoping%20this%20work%20make%20it%20easier%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%26nbsp%3Bit%20all%20seems%20to%20work%20and%20multiplies%26nbsp%3Bthe%20%25%20by%202%25%20if%20over%20a%20year%20old%20%26amp%3B%2010%25%20if%20under%2C%20I%20just%20need%20it%20to%20ignore%20this%20calculation%20if%20the%20account%20type%20is%20Route%20Cover%20Parcel%20and%20show%20%C2%A33%20instead.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20know%20a%20better%20way%20of%20doing%20this%20I%20don't%26nbsp%3Bknow%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358192%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Switch%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358192%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20if%20you%20remove%20sensitive%20information%20and%20attach%20the%20file%20here%20to%20your%20post.%20If%20that's%20not%20possible%20-%20yes%2C%20please%20contact%20me%20with%20private%20message.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358154%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Switch%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358154%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20that%2C%20that%20does%20look%20simpler.%26nbsp%3B%20I%20am%20still%20having%20trouble%26nbsp%3Bgetting%20the%20sheet%20to%20calculate%20properly%20though.%26nbsp%3B%20Would%20you%20be%20able%20to%20look%20at%20the%20sheet%20for%20me%20if%20I%20emailed%20it%20over%20to%20you%2C%20it's%20driving%20me%20nuts%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%20Once%20I%20get%20this%20right%20it%20will%20be%20fine%20for%20months%20or%20even%20years.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGiven%20the%20knowledge%26nbsp%3Byou%20have%2C%20I%20think%20it%20would%20take%20you%20a%20few%20minutes%2C%20I%20have%20spent%20about%2015%20hours%20on%20this%20on%20%26amp%3B%20off%20over%20the%20last%20few%20weeks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20very%20cheeky%20I%20know%20but%20it%20must%20be%20the%20combination%20of%20Vlookup%2C%20tables%20and%20other%20factors%20causing%20this%20issue%20%2B%20my%20limited%20knowledge%26nbsp%3Bof%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357798%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Switch%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357798%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20confirm%20if%20formula%3C%2FP%3E%0A%3CPRE%3E%3DW404%3DZ%242%3C%2FPRE%3E%0A%3CP%3Ereturns%20TRUE%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20you%20may%20simplify%20your%20entire%20formula%20as%3C%2FP%3E%0A%3CPRE%3E%3DS404*(0.025%2B0.075*(W404%3DZ%242))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Jimbobmcwalton
Contributor

Hi There,

 

I am having an issue with this formula 

 

=SWITCH(W404=Z$2,TRUE,S404*0.1,FALSE,S404*0.025)

 

Despite the cell in question matching the fixed cell Z$2 (being TRUE) the formula is calculating it as false 

 

Does this formula look incorrect?

 

 

6 Replies

Hi,

 

Please confirm if formula

=W404=Z$2

returns TRUE

By the way, you may simplify your entire formula as

=S404*(0.025+0.075*(W404=Z$2))

Hi Sergei,

 

Thanks for that, that does look simpler.  I am still having trouble getting the sheet to calculate properly though.  Would you be able to look at the sheet for me if I emailed it over to you, it's driving me nuts :)  Once I get this right it will be fine for months or even years.

 

Given the knowledge you have, I think it would take you a few minutes, I have spent about 15 hours on this on & off over the last few weeks.

 

It's very cheeky I know but it must be the combination of Vlookup, tables and other factors causing this issue + my limited knowledge of Excel.

 

Thoughts?

 

Kind Regards

Hi,

 

Better if you remove sensitive information and attach the file here to your post. If that's not possible - yes, please contact me with private message.

Hi Sergei,

 

Thank you for this.  I have attached Aprils sheet I am trying to get to work and removed any personal data.

 

The sheet works by 

Column D - User Selects an account from drop down list( from Tab Accounts) 

Column O - User Inputs Charge - Sheets works out VAT & Total 

Column R - User Enters Cost - Sheet then works out the difference in Column T & Margin in U 

Columns V, W & X are pulled also from Tab Accounts based on different account types, salesman & whether the account is a year old.

 

This all works fine.

 

Column Y - What I am trying to do is to work out a formula that can look at all the possible combinations of these and return either a % or a set £ amount (listed in column AF)

 

then Column Z needs to look at Column Y and multiply by the % or just add the £ amount if the combination of words from VWX are Route Cover, Reint, False (over a year old) 

 

Maybe I'm making it too complicated and I don't need the table on the right?  I was hoping this work make it easier 

 

Basically, it all seems to work and multiplies the % by 2% if over a year old & 10% if under, I just need it to ignore this calculation if the account type is Route Cover Parcel and show £3 instead.

 

You may know a better way of doing this I don't know

 

Thanks in advance 

 

Jim

 

 

 

 

 

 

Solution

Hi Jim,

 

Your data is not synced. For example, in some places you have "Same Day", in others "Same Day " with the space at the end. You compare text "TRUE" with logical value TRUE, etc. I tried to eliminate with

=INDEX(ProbMatrix[Commission Rate],MATCH(1, INDEX( (TRIM([@[AC Type]])=ProbMatrix[Account]) * ([@[Sales Person]]=ProbMatrix[Sales Person]) * ( ([@[Over a Year]]="TRUE")=ProbMatrix[Over a Year]),0),0))

For your helper table it's better to add one more column which says we have commission as per cent or as absolute value, when we may calculate it as

=IF(INDEX(ProbMatrix[%],MATCH(1, INDEX( (TRIM([@[AC Type]])=ProbMatrix[Account]) * ([@[Sales Person]]=ProbMatrix[Sales Person]) * ( ([@[Over a Year]]="TRUE")=ProbMatrix[Over a Year]),0),0))="Y",[@[Courier Pay]]*[@Commission],[@Commission])

I'm not sure where is the formula from which you started this conversation.

Thank you so much for this Sergei, you're a gent!