Feb 27 2019 04:57 AM
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?
Feb 27 2019 10:41 AM
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))
Feb 28 2019 02:36 AM
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
Feb 28 2019 04:15 AM
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.
Feb 28 2019 04:43 AM
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
Feb 28 2019 06:53 AM
SolutionHi 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.
Mar 05 2019 07:54 AM
Thank you so much for this Sergei, you're a gent!
Feb 28 2019 06:53 AM
SolutionHi 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.