Require a formula to automatically find the lowest figure of an information set

Question

Hi,&nbsp;I've got a data set that I'm working from on another tab - the data is a set of prices.&nbsp;I need a formula to automatically bring up the lowest priced item within that data range in a certain cell.&nbsp;Is this possible, and if so please can someone provide the formula?&nbsp;Many thanks&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

sergeibaklan · Answer

HelpandImproveAP&nbsp;
You may use&nbsp;SMALL function (microsoft.com)

helpandimproveap · Answer

Much appreciated on your swift response.

The formula works so very grateful for this.

I do have an issue though and that issue is some of the data set contain £0.00. When I've used the SMALL function, it brings up the £0.00 figure but I don't want to include this. Is there any way to exclude specific cells or remove the ones with £0.00?

Many thanks,
A

riny_van_eekelen · Answer

HelpandImproveAP&nbsp;Try this:=SMALL(data_range,COUNTIF(data_range,0)+1)&nbsp;&nbsp;

sergeibaklan · Answer

HelpandImproveAP&nbsp;
As variant
=AGGREGATE(15,6, 1/(range&lt;&gt;0)*range,1)

oliverscheurich · Answer

HelpandImproveAP&nbsp;=SMALL(IF(A1:A10&gt;0,A1:A10),1)An alternative could be above formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.&nbsp;

Forum Discussion

Require a formula to automatically find the lowest figure of an information set

5 Replies

Resources