• 513K Members
• 4,187 Online
• 611K Conversations
SOLVED

Contributor

# Excel formula Switch help

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

# Re: Excel formula Switch help

Hi,

`=W404=Z\$2`

returns TRUE

By the way, you may simplify your entire formula as

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

# Re: Excel formula Switch help

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

# Re: Excel formula Switch help

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.

# Re: Excel formula Switch help

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

Jim

Solution

# Re: Excel formula Switch help

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.

# Re: Excel formula Switch help

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

Related Conversations
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
Consolidating data from multiple worksheets
Machala Sentance in Excel on
3 Replies
Merge Cells with Different Data Types
CaffeineKing in Excel on
2 Replies