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,
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.
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!