[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

From three numbers, highlight the lowest number if it is lower than the middle number by more than 5% of the middle number

Posted on 2015-01-22
4
Medium Priority
?
126 Views
Last Modified: 2015-01-23
Hello
Other than using a rather involved series of if statements is there a formula I can use to conditionally highlight the lowest number of three numbers if it is lower than the number with the middle value by more than 20% of the number with the middle value and also conditionally highlight the highest number if is higher than the number with the middle value by more than 20% of the number with the middle value?
For example in the attached sample file, 5 would be highlighted in the first set of numbers (13 is the middle number) and both 10 and 35 would be highlighted in the second set of numbers (25 is the middle number).
Thanks for your help
C--Users-Dave-OneDrive-Documents-sample.
0
Comment
Question by:Dave
[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
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 40565585
What sort of document is that, I can't open it? It's probably easier to attach an excel workbook if you can but try this assuming three numbers in A2:C2

Select the whole range of numbers and apply this formula in conditional formatting for your first condition

=AND(A2=MIN($A2:$C2),A2<SMALL($A2:$C2,2)*80%)

and similarly for condition 2

=AND(A2=MAX($A2:$C2),A2>SMALL($A2:$C2,2)*120%)

format as required

I attach an example

regards, barry
cf-barry.xlsx
0
 
LVL 83

Expert Comment

by:David Johnson, CD, MVP
ID: 40565651
NO POINTS
Sample.xlsx
0
 

Author Closing Comment

by:Dave
ID: 40565741
Thanks!
This works great.
I had opened excel 2013 and thought I had saved it as the default excel workbook.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40567604
No problem,

Thanks Dave and David!

regards, barry
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.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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