Conditional Formatting Data Bar Percentage

[quentinlemmer]

Good Morning,
I am trying to figure out how to create the correct formula for the data gradient fill in conditional formatting to based on time spent in that zone and total duration. The cells have been formatted to indicate hours, minutes, seconds ([h]:mm:ss).

[DJunqueira]

I don't get the idea of ​​what you want.

[quentinlemmer]

Hi DJunqueira,
I want the data gradient bar to be filled in based on the percentage of time spent in a particular zone. So for example, Zone 1 is 15 min, Zone 2, 15 min and Zone 5, 30 min and the total duration was 60 min. I would want the data gradient fill to fill that cell based on the percentage of time. In this case, Zone 1 and 2 would be filled at the value of 25% each and Zone 5 would be 50%.

Hope this makes sense.

[AliGW]

Where is the data gradient bar supposed to be? There are NO CF rules on that sheet.

Please mock up MANUALLY exactly what you want to see where you want to see it.

[Marius44]

Hi
I suppose you can see here

Hi,
Mario

[AliGW]

I don't think that is what the OP wants. No chart/graph has been requested here.

[DJunqueira]

Maybe I got what you want, see the conditional format in the worksheet.

[quentinlemmer]

Hi AliGW,
I have updated the attachment to review what I am looking to do.

[AliGW]

Thanks - I feared that was the case.

You won't be able to do this: gradient bar CF can't work with relative cell references, unfortunately. The whole CF offering is long overdue an overhaul by Microsoft.

You are going to need to consider a compromise. You really don't want to flood your workbook with CF, anyway, as it will eventually slow things down to a crawl, and too much colour can be hard on the eyes and, actually, counter-productive.

[DJunqueira]

When I finally nailed it, ))

So, you seems to have solved it.

[AliGW]

I don't think so, actually. The data bars in your suggestion are not proportionate.

[DJunqueira]

I just wished to understand what he wanted and he showed the same purpose.

[AliGW]

Let me try to show you.

With the suggested data in post #3, this is what yours does:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F
2	1	2	3	4	5	Duration
3	0:15:00	0:30:00			0:15:00	1:00:00
4	0.010416667	0.020833333	0	0	0.01041667

Sheet: Sheet1

All three data bars show as 100% full, where two should show as 25% full, one as 50% full and the other two as empty. You'll need to test this yourself, as my image does not show the CF, just the values.

Does this help? The proportions are dependent on the TOTAL value in column F.

[Marius44]

HI
I had suggested a Chart because the solution I suggest (see attachment) might be what the OP is looking for.
HI,
Mario

[quentinlemmer]

Thank you AliGW, Marius44 and DJunqueira, thank you for attempting this. I echo your thoughts AliGW that CF needs a complete overhaul.

[AliGW]

There may be a VBA solution - would you like me to move this to the VBA section?

[quentinlemmer]

VBA is way beyond my capabilities. No need, thanks.

[AliGW]

OK. In that case, if that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.