Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Location: Scotland
Posts: 3
Exclamation Progress / Completion bars. Help if you like a challenge!

I’m trying to create a list of 'completion bars’ or ‘progress bars’ using data bars for a savings budget. So for example:

A1 changes in value

The flowing cells have set goals:

A2: 200
A3: 400
A4: 700

The cells next to them show the percentage of completion (how much saved up in A1 towards the goal amount):

B2: 0-100%
B3: 0-100%
B4: 0-100%

Rules:

B3 won’t start to fill until B2 is 100% full (which completes when A1 is at 200). B3 will then start to fill, reaching 100% completion when A1 is at 600 in this instance.

It’s basically so that once one goal is complete the bar will fill up and the next one down on the list will then start to fill after and so on.

I just want a simple formula with direct references so that I don’t need to keep re-conditioning data bars, setting the minimum and maximum numbers manually and having to calculate and change formulas every time I change the value of A2/A3/A4.

Hope someone can help - my brain is fried!

*UPDATE*

To make things simpler, forget I even want a data bar, can someone just work out the formula so that B2/B3/B4 shows the percentage from 0 to 100. That way I can just create a data bar myself with minimum value 0 and maximum value 100.

Last edited by Callum : June 2nd 15 at 11:25 AM Reason: Clarification
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Progress / Completion bars. Help if you like a challenge!

Hi,

Am Tue, 2 Jun 2015 00:32:42 +0100 schrieb Callum:

A2: 200
A3: 400
A4: 700

The cells next to them show the percentage of completion (how much saved
up in A1 towards the goal amount):

B2: 0-100%
B3: 0-100%
B4: 0-100%


look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Data Bar"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Junior Member
 
Location: Scotland
Posts: 3
Exclamation

Thank's for the effort Claus B. B4 however doesn't function correctly, it fills to 100% when A1=700 instead of A1=1300 (as it should only be full once B2 and B3 are full which means B4=0 when A1=600)

I actually managed to calculate a formula that gives me a close effect to what I’m after, where B2/B3/B4 fill to 100% when A1 is at the correct value. However they don’t = 0 when they should and are not accurate from 0-100 until they reach 100. Here’s what I done:

[B2] =A1/(A2)*100

[B3] =(A1/(A3+A2)*100)-(100-A1/(A3+A2)*100)

[B4] =(A1/(A4+A3+A2)*100)-(100-A1/(A4+A3+A2)*100)

You can download it he https://www.dropbox.com/s/2kp5cmxagx...0Bar.xlsx?dl=0

Hopefully this helps clarify where I got stuck and what I’m trying to achieve.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Progress / Completion bars. Help if you like a challenge!

Hi Callum,

Am Tue, 2 Jun 2015 12:03:12 +0100 schrieb Callum:

Thank's for the effort Claus B. B4 however doesn't function correctly,
it fills to 100% when A1=700 instead of A1=1300 (as it should only be
full once B2 and B3 are full which means B4=0 when A1=600)


Sorry for the misunderstanding.

Look again:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Data Bar"
I changed the absolute values that it is easier to handle.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Progress / Completion bars. Help if you like a challenge!

Hi again,

Am Tue, 2 Jun 2015 14:16:10 +0200 schrieb Claus Busch:

Look again:
https://onedrive.live.com/redir?resi...=folder%2cxlsm


have another look. I changed it now with your original data.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #6   Report Post  
Junior Member
 
Location: Scotland
Posts: 3
Exclamation

As soon as I change the value of A2, A3 or A4 it messes up?
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Progress / Completion bars. Help if you like a challenge!

Hi Callum,

Am Tue, 2 Jun 2015 20:14:22 +0100 schrieb Callum:

As soon as I change the value of A2, A3 or A4 it messes up?


you first wrote that only A1 will change.
Have another look in OneDrive. I fixed that problem.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
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
Customise error bars on individual bars in a bar chart 2007 Millie Charts and Charting in Excel 2 April 25th 09 04:16 AM
NEED Help - How to capture the Event's progress in Progress Bar Sriram Excel Programming 6 August 22nd 06 12:04 PM
How can I change a graph with 2 bars,2 lines to 3 bars, 1 line? IOWAJulie Charts and Charting in Excel 3 October 19th 05 11:13 PM
Progress Bars Information Hog[_4_] Excel Programming 2 August 22nd 05 11:51 PM
Lost all my menu bars and tool bars CathyJ Excel Discussion (Misc queries) 3 August 8th 05 12:49 PM


All times are GMT +1. The time now is 06:14 AM.

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

About Us

"It's about Microsoft Excel"