CODEWITHSUNDEEP
excelexcel-formula
orad
orad

Reputation: 16056

Excel conditional formatting with Data Bars of different ranges

For some reason the length of data bars I get is not correct when using formulas that give different Minimum and Maximum values for each bar. Here I'm using =INDIRECT("A" & ROW()) formula for the Minimum value and =INDIRECT("B" & ROW()) for Maximum value of the data bar.

So for example in Row 3 below, the data bar should be in a (25, 230) range. However, the data bar showing Current value of 200 which should be closer to End bound is shown closer to the Start bound. Only Row 2 with longer range of (25, 700) looks to be showing data bar correctly. Why is that? How can I make data bars have independent ranges?

Sample data

Thanks!

Upvotes: 4

Views: 5454

Answers (1)

Axel Richter
Axel Richter

Reputation: 61862

Why you have tried to use INDIRECT? Because, if you try to use relative addresses in conditional formatting data bars, you get the error, that relative relations are not allowed there. But ROW() also is a relative address.

You could compute %-Values in column D as:

=(C2-A2)/(B2-A2)

Then put the default conditional formatting data bars on D2:D[n] and show only the bars there.

Greetings

Axel

Upvotes: 7

Related Questions