Using the Excel SMALL function with filtering criteria AND ignoring zeros

1
votes

I have a list of prices for products that are variations (different size, color) of multiple parent product. Some products (for reasons not important to this question) have a price of zero.

I'm trying to write an excel formula that will return the lowest price for each parent product that is not zero

I've got this far:

=IF(SUMIFS(I:I,A:A,A3)=0,0,IF(AND(SUMIFS(I:I,A:A,A3)>0,MINIFS(I:I,A:A,A3)>0),MINIFS(I:I,A:A,A3),SMALL(IF(A:A=A3,I:I),2)))

Where I:I is the price column and A:A is the parent reference column

This works in all cases except where I have multiple variations of a parent product that have a zero value.

I need variables in a SMALL or MINIF function that will: A. Only include the values for a single parent product in the calculation B. Ignore any zeros in the calculation of the lowest value

Any help would be appreciated.

4
excelformulas
Can't you simply replace the 0s with a high number or simply remove the rows? would make your life much easier. Minifs is not a normal excel formula. Are you having some VBA function named that? If yes, we will need to look at its code - Raunak Thomas
Why not use a pivot table to get the minimum from each group, instead of rewriting built-in functionality? - ashleedawg
@ashleedawg 'Traditional' PivotTables would still show a min of zero, which the OP wants to ignore when calculating the minimum. - jeffreyweir
@RVPSRichard What version of Excel do you have. If you have Excel 2013 or later you could use a PivotTable based on something called the Data Model to do this, using something called DAX measures. (Earlier versions would require you to install the free PowerPivot add-in to do this using a PivotTable). - jeffreyweir
@jeffreyweir Absolutely not true. I just confirmed by taking a couple columns of values (with zero's and blanks) and in under 45 seconds had a pivot table showing minimums by group, excluding zero's . - ashleedawg

4 Answers

2
votes

You can use a Pivot Table to easily create a dynamic list of minimums. Blank cells are ignored by default, and zero's (or any other number you want) can be excluded a few ways; I think the easiest would be using the Pivot Table's Filter feature.

They say a picture is worth a thousand words so an animated GIF must be worth a million... :)

img

More Information:

1
votes

If the input data is sorted based on the parent reference level you can simply

  • use offset to find the required parent reference cells containing the price
  • Find the k-th smallest value using the SMALL formula (i.e. the smallest value)
  • Put a if condition to give you the 2nd smallest value if the smallest is 0

Place this in the 1st row

=IF(SMALL(OFFSET($I$1,MATCH(C1,$C:$C,0)-1,0,COUNTIF($C:$C,C1)),1)=0,SMALL(OFFSET($I$1,MATCH(C1,$C:$C,0)-1,0,COUNTIF($C:$C,C1)),2),SMALL(OFFSET($I$1,MATCH(C1,$C:$C,0)-1,0,COUNTIF($C:$C,C1)),1))
0
votes

You can use a 'traditional' PivotTable to do this. See ashleedawg's great answer.

You can also use a 'OLAP' PivotTable that's based off something called the Data Model to do it, that has a small advantage over the 'traditional' PivotTable in that you don't need to clear the PageFilter after refreshing. (More on this at the bottom of my answer).

Any Excel version from Excel 2013 later has the DataModel built in by default, and you also get access to the DataModel in 2010 if you install Microsoft's free PowerPivot add-in. Using the DataModel, here's what you need to do:

Turn your data into an Excel Table using the Ctrl + T keyboard shortcut:

enter image description here

Make a PivotTable out of it, but be sure to check the Add this data to the Data Model option:

enter image description here

Add Group to the Rows pane of the PivotTable, then right-click on Table1 in the fields pane and select Add Measure...:

enter image description here

Give the new Measure a name (I've called it Min Without Zero) and type =CALCULATE(MIN(Table1[Price]),Table1[Price]>0) in the Formula box then push OK:

enter image description here

Put a tick in the Min Without Zero checkbox in the PivotTable fields list.

enter image description here

Bing! The PivotTable shows the min (excluding zero) of each group by price.

Edit: I'd probably use ashleedawg's answer if I was doing this myself. But there's one caveat: If you add additional data in future and refresh, then you need to clear the filter and reapply the zero exclusion to be absolutely sure that any new numbers in the data are included. By way of a demonstration, if I filter out zero from a PageField, then I get just the result I expect:

enter image description here

...and here's how the filter looks:

enter image description here

But if I add new data and refresh, note that nothing has changed in the PivotTable:

enter image description here

...and if I look at the filter I can see why:

enter image description here

That's because when you deselect something from a filter, you're not actually saying "Give me everything except this thing" but rather "Give me the other things currently visible in this list, but leave any new things unselected."

Easily enough fixed: Just clear the filter and deselect zero again (or write some VBA code to do it automatically on refresh).

0
votes

If you want a formula approach, you can use an IF statement to return a value if that value is over zero, and otherwise return FALSE, as functions like MIN conveniently ignore FALSE.

As per my other answer I recommend you turn the data into an Excel Table first with the Ctrl + T keyboard shortcut, as shown below. Then in a summary table you can use the following array-entered formula in E2:

=MIN(IF(Table1[Price]>0,IF(Table1[Group]=D2,Table1[Price])))

(Array-entered means you push Ctrl + SHIFT+ ENTER instead of pushing ENTER like you usually do)

And here's the result:

enter image description here