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

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

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
Dave
Asked:
Dave
  • 2
1 Solution
 
barry houdiniCommented:
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
 
David Johnson, CD, MVPOwnerCommented:
NO POINTS
Sample.xlsx
0
 
DaveAuthor Commented:
Thanks!
This works great.
I had opened excel 2013 and thought I had saved it as the default excel workbook.
0
 
barry houdiniCommented:
No problem,

Thanks Dave and David!

regards, barry
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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