• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Find the 2 Lowest of 3 numbers, nested If statement

Hi,

I had a request to change how I was figuring out skin measurements in a physical test. There is a minimum of 2 measurements and possibly a 3rd (If you don't fall within 1 cm from the 2nd and 1st measurement, they measure a 3rd time). If there are 2 measurements (using my count formula), then I need to take the average of those 2 numbers. If there is a count of 3 measurements, I need to take the 2 lowest measurements and average the 2.

I came up with this, but I think the small() is incorrect. It looks like it is returning the 2nd smallest number, not the lowest and 2nd to lowest numbers.

'=IF(A2<>"",IF(J2=2,AVERAGE(A2:B2),IF(J2=3,AVERAGE(SMALL(A2:C2,2)),"")))

Thanks for any  help! Brent
EE-find-the-two-lowest-numbers.xlsx
0
bvanscoy678
Asked:
bvanscoy678
  • 3
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
Use this formula:

=IF(COUNTA(A2:C2)=2,AVERAGE(A2:B2),AVERAGE(SMALL(A2:C2,1),SMALL(A2:C2,2)))

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
It turns out that SMALL ignores blank cells so this works too:

=AVERAGE(SMALL(A2:C2,1),SMALL(A2:C2,2))

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
This formula will display blank if there are only one or no measurements:

=IF(COUNTA(A2:C2)>1,AVERAGE(SMALL(A2:C2,1),SMALL(A2:C2,2)),"")

Kevin
0
 
bvanscoy678Author Commented:
The last one is perfect! I can eliminate one of my columns and merge the two. Thank you for the quick response!

Brent
0
 
bvanscoy678Author Commented:
thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now