Solved

# Find the 2 Lowest of 3 numbers, nested If statement

Posted on 2013-07-01
Medium Priority
279 Views
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
Question by:bvanscoy678
• 3
• 2

LVL 81

Expert Comment

ID: 39292029
Use this formula:

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

Kevin
0

LVL 81

Expert Comment

ID: 39292032
It turns out that SMALL ignores blank cells so this works too:

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

Kevin
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 39292035
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

Author Comment

ID: 39292039
The last one is perfect! I can eliminate one of my columns and merge the two. Thank you for the quick response!

Brent
0

Author Closing Comment

ID: 39292041
thanks!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.