Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
As soon as I change the value of A2, A3 or A4 it messes up?
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Customise error bars on individual bars in a bar chart 2007 | Charts and Charting in Excel | |||
NEED Help - How to capture the Event's progress in Progress Bar | Excel Programming | |||
How can I change a graph with 2 bars,2 lines to 3 bars, 1 line? | Charts and Charting in Excel | |||
Progress Bars | Excel Programming | |||
Lost all my menu bars and tool bars | Excel Discussion (Misc queries) |