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

%3CLINGO-SUB%20id%3D%22lingo-sub-3287840%22%20slang%3D%22en-US%22%3ERequire%20a%20formula%20to%20automatically%20find%20the%20lowest%20figure%20of%20an%20information%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3287840%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20a%20data%20set%20that%20I'm%20working%20from%20on%20another%20tab%20-%20the%20data%20is%20a%20set%20of%20prices.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20to%20automatically%20bring%20up%20the%20lowest%20priced%20item%20within%20that%20data%20range%20in%20a%20certain%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%2C%20and%20if%20so%20please%20can%20someone%20provide%20the%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3287840%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3288059%22%20slang%3D%22en-US%22%3ERe%3A%20Require%20a%20formula%20to%20automatically%20find%20the%20lowest%20figure%20of%20an%20information%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3288059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1341687%22%20target%3D%22_blank%22%3E%40HelpandImproveAP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSMALL(IF(A1%3AA10%26gt%3B0%2CA1%3AA10)%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAn%20alternative%20could%20be%20above%20formula.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3288057%22%20slang%3D%22en-US%22%3ERe%3A%20Require%20a%20formula%20to%20automatically%20find%20the%20lowest%20figure%20of%20an%20information%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3288057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1341687%22%20target%3D%22_blank%22%3E%40HelpandImproveAP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DAGGREGATE(15%2C6%2C%201%2F(range%26lt%3B%26gt%3B0)*range%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3287992%22%20slang%3D%22en-US%22%3ERe%3A%20Require%20a%20formula%20to%20automatically%20find%20the%20lowest%20figure%20of%20an%20information%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3287992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1341687%22%20target%3D%22_blank%22%3E%40HelpandImproveAP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CP%3E%3DSMALL(data_range%2CCOUNTIF(data_range%2C0)%2B1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3287913%22%20slang%3D%22en-US%22%3ERe%3A%20Require%20a%20formula%20to%20automatically%20find%20the%20lowest%20figure%20of%20an%20information%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3287913%22%20slang%3D%22en-US%22%3EMuch%20appreciated%20on%20your%20swift%20response.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20formula%20works%20so%20very%20grateful%20for%20this.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20do%20have%20an%20issue%20though%20and%20that%20issue%20is%20some%20of%20the%20data%20set%20contain%20%C2%A30.00.%20When%20I've%20used%20the%20SMALL%20function%2C%20it%20brings%20up%20the%20%C2%A30.00%20figure%20but%20I%20don't%20want%20to%20include%20this.%20Is%20there%20any%20way%20to%20exclude%20specific%20cells%20or%20remove%20the%20ones%20with%20%C2%A30.00%3F%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%2C%3CBR%20%2F%3EA%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3287844%22%20slang%3D%22en-US%22%3ERe%3A%20Require%20a%20formula%20to%20automatically%20find%20the%20lowest%20figure%20of%20an%20information%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3287844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1341687%22%20target%3D%22_blank%22%3E%40HelpandImproveAP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fsmall-function-17da8222-7c82-42b2-961b-14c45384df07%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESMALL%20function%20(microsoft.com)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I've got a data set that I'm working from on another tab - the data is a set of prices.

 

I need a formula to automatically bring up the lowest priced item within that data range in a certain cell.

 

Is this possible, and if so please can someone provide the formula?

 

Many thanks 

 

 

 

 

5 Replies
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

@HelpandImproveAP 

Try this:

=SMALL(data_range,COUNTIF(data_range,0)+1)

 

 

@HelpandImproveAP 

As variant

=AGGREGATE(15,6, 1/(range<>0)*range,1)

@HelpandImproveAP 

=SMALL(IF(A1:A10>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.