Help with Index/ Small formula to return valid values and not give a blank row for cells that don't

Bukemm

New Member
Joined
May 13, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I've been googling and reading threads on here describing how to use Index/Small functions to return a cells values if it meets a given criteria without giving lots of blank rows where the criteria doesn't match, as it would with an IF formula

I haven't been able to adapt these to fit my case, so hoping for some advice

Essentially I have a report that has 60k+ lines showing product revenues and costs among other data. I want to add a summary tab that shows the company, material, revenue and cost for any part that has revenue above $50k, and costs higher than revenue (I'll use a cell reference for the $50k figure so it can be adjusted). I can do this with nested if's, but I'll end up with 60000 rows of blank cells for 20 valid lines of data, and I want it to be dynamic so the summary tab works if the user changes the criteria used for the main body of data (for example, refreshing it for a different year / different set of companies)

I've attached an image of some example data. Other threads indicate this can be done with something like:
{=IFERROR(INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=TRUE,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($1:1))),"")}

As a first step I wanted to see if I could get it to pull through all the lines with revenue above 50k, so I've tried
=IFERROR(INDEX('BW Data'!$A$1:$J$100000,SMALL(IF($C$1:$C$100000>50000,ROW($C$1:$C$100000)-ROW($C$1)+1,Rows($1:1)),),"")

but that doesn't work (I get an error about too few arguments), and i don't understand it well enough to begin to troubleshoot. Ideally I want to replace the IF>50,000 with an IF(AND) to say if revenue Greater than 50,000 and -Cost > Revenue, display the company in Cell A5 on the summary sheet, material in B5, revenue in C5, cost in D5, and drag it down so the next valid result populates in A:D6 etc.

Any advice greatly appreciated!
 

Attachments

  • Excel Example Data.png
    Excel Example Data.png
    26.3 KB · Views: 3

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,546
Office Version
  1. 365
Platform
  1. Windows
For that first criteria try
Excel Formula:
=IFERROR(INDEX('BW Data'!$A$1:$A$100000,AGGREGATE(15,6,(ROW('BW Data'!$C$1:$C$100000)-ROW('BW Data'!$C$1)+1)/($C$1:$C$100000>50000),ROWS(A$2:A2))),"")
Assumes the formula is going in A2 if not change the A$2:A2 to suit.
 
Solution

Bukemm

New Member
Joined
May 13, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
For that first criteria try
Excel Formula:
=IFERROR(INDEX('BW Data'!$A$1:$A$100000,AGGREGATE(15,6,(ROW('BW Data'!$C$1:$C$100000)-ROW('BW Data'!$C$1)+1)/($C$1:$C$100000>50000),ROWS(A$2:A2))),"")
Assumes the formula is going in A2 if not change the A$2:A2 to suit.

Thank you so much for the quick reply. That works :)

May I ask your advice on how to improve the $C$1:$C$100000>50000 logic so that it would only show if the revenue was above $50,000, and the revenue - cost was also less than 0?
 

Bukemm

New Member
Joined
May 13, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Sorry for the double-post, couldn't see an option to edit.

I think I've answered my own question - I should be able to use this logic onto a 'Working' tab, add a formula to do the revenue - cost section, and then make a third summary tab that pulls through from that using the same logic but checking that new field for values <0

Busy mocking it up now, but thanks again for your support!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,546
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.

For 60k rows, a helper column to do the revenue - cost, is probably the best way to do it.
 

Forum statistics

Threads
1,175,662
Messages
5,898,753
Members
434,729
Latest member
Tej92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top