Excel formula Switch help

[Jimbobmcwalton]

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?

[#2]

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

[#3]

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

[#4]

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.

[#5]

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

[#6 · Accepted 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.

[#7]

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