+ Reply to Thread
Results 1 to 10 of 10

Data bars in merged cells not showing correct length

  1. #1
    Registered User
    Join Date
    04-29-2019
    Location
    Tampa, Florida
    MS-Off Ver
    Office 365
    Posts
    8

    Data bars in merged cells not showing correct length

    Hi, I'm trying to show a data bar in a merged cell. I want the bar to be the correct length based on a min and max range listed above the merged cell. See image below. Based on the value in the merged cell the length of the data bar should almost reach the midpoint (orange line) of the merged cells but it is not. How can I get the length to be accurate? The width of each of the three columns that make up the merged cell is equal.

    data bars 1.jpg

    The image below shows the conditional formatting data bar settings.

    data bars 2.jpg

    Thanks in advance for your help.
    Last edited by sgoodrich; 02-15-2021 at 02:41 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data bars in merged cells not showing correct length

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Data bars in merged cells not showing correct length

    Simple remedy - do not use merged cell - they are specially designed to frustrate the unwary.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data bars in merged cells not showing correct length

    It's a crying shame that EXcel does not (AFAIK) allow you to add "Centre Across Selection" to the Quick Access Toolbar. Merging cells is just too easy... Big, Bold and easily found.

  5. #5
    Registered User
    Join Date
    04-29-2019
    Location
    Tampa, Florida
    MS-Off Ver
    Office 365
    Posts
    8
    Quote Originally Posted by Glenn Kennedy View Post
    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Here is a sample file.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-29-2019
    Location
    Tampa, Florida
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Data bars in merged cells not showing correct length

    Hi torachan, I agree, merged cells cause all kinds of frustration. I can't seem to find a way around using them in this scenario. I tried using center across selection and it does not center the conditional formatting. It just centers any text in the cells.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data bars in merged cells not showing correct length

    RoFLAO.

    This should teach me never to believe what I read.

    There is NOTHING wrong with your databar, or your midpoint line. It is your maths that is faulty. The mid-point between 104K and 156 K is not 121K... but is 130K. Change the value in A2 to the REAL mid-point and there is NO problem.

    You are a victim of a) wonky maths and b) the suppressed zero in your data bar... starting the scale at 104K, which nmade it LOOK further away from the midpoint than it REALLY is.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data bars in merged cells not showing correct length

    In honesty, I should also add that it took me 20 minutes and a lot of Googling to discover the problem....

  9. #9
    Registered User
    Join Date
    04-29-2019
    Location
    Tampa, Florida
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Data bars in merged cells not showing correct length

    lol. Thanks Glenn I guess that's what you call an ID10T error. The min/mid/max pull from a database so I didn't even think to check them. Thanks for your detective work.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data bars in merged cells not showing correct length

    Afraid so... a PICNIC issue...

    Problem In Chair, Not In Computer

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Variable Length Array does not bring correct data
    By AZ-XL in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2017, 08:20 AM
  2. Replies: 1
    Last Post: 03-14-2017, 03:00 PM
  3. Replies: 7
    Last Post: 10-20-2015, 01:51 PM
  4. Error bars not showing up for 1-variable data
    By Kinzie in forum Excel General
    Replies: 0
    Last Post: 01-21-2015, 05:39 PM
  5. Replies: 2
    Last Post: 12-30-2014, 01:33 PM
  6. Replies: 0
    Last Post: 11-25-2014, 07:08 AM
  7. Bars in Gantt chart showing values of certain cells?
    By darelooney in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2008, 10:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1