Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 24th 19, 04:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 5
Default Highlighting closest value in section.


C D E F G H I
8) 39.4% 36.9% 24.70% 49.00% 49% 24.80% 47.70%

How do I highlight in conditional formatting the closes value in C8:H8 to I8?

Thanks in advance!

  #2   Report Post  
Old May 24th 19, 05:00 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,727
Default Highlighting closest value in section.

Hi,

Am Fri, 24 May 2019 08:48:38 -0700 (PDT) schrieb
:

C D E F G H I
8) 39.4% 36.9% 24.70% 49.00% 49% 24.80% 47.70%

How do I highlight in conditional formatting the closes value in C8:H8 to I8?


select C8:H8 and use following formula in conditonal formatting:
=ABS($I$8-C8)=MIN(ABS($I$8-$C$8:$H$8))


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old May 24th 19, 05:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 5
Default Highlighting closest value in section.

It doesn't appear to be working properly. I am using Google Sheets, is that the issue? It highlights all the variables rather than the one closest to I8.



  #4   Report Post  
Old May 24th 19, 05:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,727
Default Highlighting closest value in section.

Hi,

Am Fri, 24 May 2019 09:11:34 -0700 (PDT) schrieb
:

It doesn't appear to be working properly. I am using Google Sheets, is that the issue? It highlights all the variables rather than the one closest to I8.


in Excel it works fine. Ask the Google support for a solution or you
have to use a helper row.


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Old May 24th 19, 05:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,727
Default Highlighting closest value in section.

Hi again,

Am Fri, 24 May 2019 09:11:34 -0700 (PDT) schrieb
:

It doesn't appear to be working properly. I am using Google Sheets, is that the issue? It highlights all the variables rather than the one closest to I8.


have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgeMv91yjfQu5D4QRPA


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Old May 24th 19, 05:32 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 5
Default Highlighting closest value in section.

I'm looking over it. Thank you for your help.
  #7   Report Post  
Old May 24th 19, 05:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 5
Default Highlighting closest value in section.

Where in your example do you view, edit, and add custom formulas in the conditional formatting?
  #8   Report Post  
Old May 24th 19, 06:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,727
Default Highlighting closest value in section.

Hi,

Am Fri, 24 May 2019 09:58:50 -0700 (PDT) schrieb
:

Where in your example do you view, edit, and add custom formulas in the conditional formatting?


you can see the rules only in Excel local. In OneDrive and Excel Online
the rules can't be seen.
The formula for row 8 is the formula I posted.
The formula for row 12 is:
=C13=MIN($C$13:$H$13)


Regards
Claus B.
--
Windows10
Office 2016
  #9   Report Post  
Old May 24th 19, 06:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 5
Default Highlighting closest value in section.

On Friday, May 24, 2019 at 10:48:42 AM UTC-5, wrote:
C D E F G H I
8) 39.4% 36.9% 24.70% 49.00% 49% 24.80% 47.70%

How do I highlight in conditional formatting the closes value in C8:H8 to I8?

Thanks in advance!


The Helper row and that last formula worked. Thanks you


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to determine the closest value? Eric Excel Discussion (Misc queries) 2 November 5th 08 03:05 PM
How do I: If Pie Section Label = X, Make Pie Section Color = Y MikeZz Excel Programming 2 September 3rd 08 08:35 PM
Array Lookup to Find Closest Date and Next Closest Date [email protected] Excel Worksheet Functions 7 November 7th 07 03:04 AM
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. Jamie Furlong Excel Discussion (Misc queries) 6 August 28th 05 09:27 PM
Closest-to function davidoo2005 Excel Programming 1 July 19th 04 06:03 AM


All times are GMT +1. The time now is 10:55 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017