?
Solved

Find the 2 Lowest of 3 numbers, nested If statement

Posted on 2013-07-01
5
Medium Priority
?
272 Views
Last Modified: 2013-07-01
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
Comment
Question by:bvanscoy678
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:zorvek (Kevin Jones)
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

by:
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

by:bvanscoy678
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

by:bvanscoy678
ID: 39292041
thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question