Log In
Register
Home
I have the following formula in column F of my attached Excel file.
=IF([@Override]="Yes", "Yes", IF([@Override]="No", "No", IFERROR(IF(AND([@Supplier]<>"", [@[Comparative Price]]<>"", [@[Comparative Price]]=AGGREGATE(15,6,[Comparative Price]/(([Line No]=[@[Line No]])*[Auxiliary]),1)), "Yes", "No"), "No")))
Check the attached file for an explanation of my issue.
Thank you for your assistance.
The 2 files I uploaded previously should be ignored. Use the new one attached.
Trusted Members
October 17, 2018
Offline
Both these files are identical (?) at least by name?
And further more, your topic question is an=bout 'ignore HIDDEN cells' but ... there are no hidden cells
Which version of Excel?
Trusted Members
October 17, 2018
Offline
The rows are hidden when you use the splicer, now I see it but your formula to check Yes/No in F, isn't it much easier to to first add datavalidation to column E so that only Yes/No can be entered?
After thatm why all the complication. if E=Yes the F= No ?
Hi Hans, in case you missed my previous post, the correct file is attached.
I'll explain further; in my live application, column E contains data validation so that only Yes or No can be selected.
The purpose of column F is to find the Lowest value for the relevant Line No for visible rows.
If you filter out Supplier "Ziggy" the results are correct. But if you make "Ziggy" visible, you'll note that in the column E for "Ziggy" the cell values are "Yes". This means Override the Lowest value found in column F. But now in column F, the Lowest value still shows "Yes" and "Ziggy" also "Yes". This is the issue as only "Ziggy" should show "Yes".
I hope the above makes sense.
Moderators
January 31, 2022
Online
The formula in F seems to return Yes for the smallest value in each group of visible line numbers where certain other cells are not empty. The formula does exactly that in F11. If that's not correct you need to rewrite the formula.
And the reason for F14 to return Yes is because the very first IF checks if Override = Yes, then Yes. The formula stops evaluating and does not care if the rows are visible or not.
In short, I don't really understand what you are trying to achieve. some more clarification is needed.
Riny, thanks for responding.
Let me explain a bit more. "The formula in F seems to return Yes for the smallest value in each group of visible line numbers where certain other cells are not empty. " Totally correct, however, if there is Yes in column E (and there will only be 1 per group) for a visible row, the formula in F should return Yes for that row and ignore finding the smallest value. Furthermore, if the row that contains Yes in column E gets filtered out (hidden), then the formula in F should work as normal, that is, finding the smallest value.
I hope my explanation makes sense.
Moderators
January 31, 2022
Online
Working on it. I'll be back!
Moderators
January 31, 2022
Online
I took the liberty to add some more helper columns (shaded orange) to make it easier to follow (mostly for myself). and I also changed the logic of determining what's visible and what is not. See if the Result column is what you need.
Hi Riny, fantastic it works in that Result column. It would be nice if the same could be achieved without the additional helper columns Is that possible?
Thank you very much for the help you have provided so far.
Moderators
January 31, 2022
Online
Sure, but the formula will become quite long and ugly. Which Excel version do you use?
Hi Riny, I'm using Excel 365.
Thanks once again for this brilliant solution.
Just for interest, we used VBA up to now to achieve the results that you did with formulas. The performance using formulas is much better than using VBA
.
Moderators
January 31, 2022
Online
Good to hear. Not a big fan of VBA anyway.
Trusted Members
October 17, 2018
Offline
Grat solution, and Riny, you say you're not a big fan of VBA, this, seen the newest developments and functionality which Microsoft is building in I agree and I am using it more often too but there are so many pre 365 - 2021 users that VBA is in manycases the best soltuion and well written doesn't have to be a setback unless limited by the user's netwoks
Trusted Members
Moderators
November 1, 2018
Offline
For your original data set with the Auxiliary column in place, perhaps:
=IF([@Override]="Yes","Yes",IF(OR([@Supplier]="",[@[Comparative Price]]=""),"No",IF(COUNTIFS([Line No],[@[Line No]],[Auxiliary],1,[Override],"Yes"),"No",IF(MIN(FILTER([Comparative Price],([Line No]=[@[Line No]])*([Auxiliary]=1)))=[@[Comparative Price]],"Yes","No"))))
1 Guest(s)
