Forum Discussion
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.
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
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!