Not sure how to explain...

[mvirgilio53]

I have a list of buildings and their energy usage trend. the list looks like this:


building % variance

xyz 12%

xzy -10%

yyz 30%


I need a formula that will pick out the buildings that have met a certain condition. So for example if I want to pick up all buildings that increased by >5% I would want to end up with.


building % variance

xyz 12%

yyz 30%


Most importantly I want to skip the buildings that don't meet the criteria, not just result with blank cells. Any ideas? Thanks in advance

 

[mvirgilio53]

I know I can sort and easily do that, but I would like a formula to do it for a dashboard I'm working on that can easily and automatically update.

 

[Hui]

Add a helper column in which you rank the buildings on their variance

Then use a sumproduct to retrieve the buildings and other data based on the Rank and Variance

=sumproduct(+1*(Rank Column = 1..x)*(Variance Col > 5))

for 1..x use (row()-row(Ax)) where Ax is 1 cell above the top of the table

 

[mvirgilio53]

That's a great solution! I actually found a different solution that took about 3 lines of formulas.

 

[pokemont17]

Thanks you for the post.

Hi guys, Im a newbie. Nice to join this forum.


__________________

Watch Resident Evil: Afterlife Online Free