02-27-2019 04:57 AM
02-27-2019 04:57 AM
I am having an issue with this formula
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?
02-27-2019 10:41 AM
Please confirm if formula
By the way, you may simplify your entire formula as
02-28-2019 02:36 AM
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.
02-28-2019 04:15 AM
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.
02-28-2019 04:43 AM
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
02-28-2019 06:53 AMSolution
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.