PLEASE, I need help here! I am looking for a way to highlight the best price on my products list. Using Conditional Formatting, I could do that. But it highlights the ZEROS. Here's what I am doing - =H2=AGGREGATE(5,7,H$2:H$819) - and this is really good, works whit and without filtering. But it highlights zeros instead of best prices... So, after have explored for a while, I saw another video of yours and I end up with this - =AGGREGATE(15,7,H$2:H$819/(H$2:H$819>0),1)=H2 WOW, no more zeros selected by the conditional formatting! The problem is, when I use FILTER, this solution disappears. The lower price is not highlighted anymore. And I have NO IDEA why. Probably, it CAN'T ignore hidden rows anymore. Could you please help me with this? Of course, my prices are in a vertical position (columns). Just like the products. I am selecting on a column of prices (there are three) and trying to apply conditional formatting to highlight the best price mostly for a filtered list. So, I need the best price highlighted and "ignore hidden rows" and "ignore equal to zero" at the same time.
Yes, AGGREGATE is without a doubt, my favorite new function!!!
I am glad that the videos help!
OMG, Thank you SO much for this. Big thumbs up and subscribing fo' sho'! You have made my life so much easier!
You are welcome!
Cool, had no idea about the 109 within SUBTOTAL is that new?
No, it has been around for a long time - back in the 1990s.
AGGREGATE function kicks butt on SUBTOTAL; easier to use and more practical for ignoring hidden rows when calculating (AVG, SUM, MIN, MAX)
hi im a pretty basic user but what can I use to countif whilst ignoring hidden columns?
subtotal 109 will not include the numbers in hidden rows in the computation (sum)
PLEASE, I need help here!
I am looking for a way to highlight the best price on my products list.
Using Conditional Formatting, I could do that. But it highlights the ZEROS.
Here's what I am doing - =H2=AGGREGATE(5,7,H$2:H$819) - and this is really good, works whit and without filtering. But it highlights zeros instead of best prices...
So, after have explored for a while, I saw another video of yours and I end up with this -
=AGGREGATE(15,7,H$2:H$819/(H$2:H$819>0),1)=H2
WOW, no more zeros selected by the conditional formatting!
The problem is, when I use FILTER, this solution disappears.
The lower price is not highlighted anymore. And I have NO IDEA why.
Probably, it CAN'T ignore hidden rows anymore.
Could you please help me with this?
Of course, my prices are in a vertical position (columns). Just like the products.
I am selecting on a column of prices (there are three) and trying to apply conditional formatting to highlight the best price mostly for a filtered list.
So, I need the best price highlighted and "ignore hidden rows" and "ignore equal to zero" at the same time.
❤️