Forum Discussion
Solved
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?
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.
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.
