[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Find the 2 Lowest of 3 numbers, nested If statement

Posted on 2013-07-01
5
Medium Priority
?
274 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

656 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