Data bar work the opposite way?

[ikeevens]

Is there a way to have a data bar work the opposite way? If a cell is 0% I want it to be completely red if it’s 100% no color.

Here is how my table is set up.

Item:


User entered data.

Purchase Date:
User entered date.

Years in Service:
=2016-YEAR([Date Purchased])

Purchase Price:
User entered price.

Amount Depreciated: This is based on computers depreciating 20% each year over five years.
=IF([Years in Service]=1,[Purchase Price]*20%,IF([Years in Service]=2,[Purchase Price]*40%,IF([Years in Service]=3,[Purchase Price]*60%,IF([Years in Service]=4,[Purchase Price]*80%,IF([Years in Service]=5,[Purchase Price]*100%,IF([Years in Service]>5,[Purchase Price]*100%,0))))))

Estimated Current Value:
=[Purchase Price]-[Amount Depreciated]

This is where I would like the gradient data bar to display all red if completely depreciated or fill half if the item is only half way depreciated and so forth. The way it works now is if it’s completely depreciated the cell has no color and if it’s not deprecated at all the cell if full of color.

Thanks for any help.

[xor]

I don't see an easy way to do what you want in Excel, but the attached may give some inspiration.

[ikeevens]

I don't know enough about Excel.

What determines how much of the cell gets filled?

What about something like this.
If Years in Service cell = 0 the Estimated Current Value cell has no color.
If Years in Service cell = 1 the Estimated Current Value cell has 20% color.
If Years in Service cell = 2 the Estimated Current Value cell has 40% color.
If Years in Service cell = 3 the Estimated Current Value cell has 60% color.
If Years in Service cell = 4 the Estimated Current Value cell has 80% color.
If Years in Service cell > 4 the Estimated Current Value cell has 100% color.

Right now I have it if
Years in Service cell = 4 Estimated Current Value cell has gradient red to white.
If Years in Service cell > 4 Estimated Current Value cell has solid red.
Otherwise the cell has no color.

This provides a pretty good visual as to what is going out of service and what is out of service. I guess I could use different shades of red lighter to darker as things get older?

[xor]

Don't know if this is any better.

[ikeevens]

That's pretty slick. Although, I need to have the Estimated Current Value show in the cell as well. I have Purchase Price - Amount Depreciated = Estimated Current Value. Not sure how that would work with the font being webdings.

I used conditional formatting and if the years in service is less then 4 no color. If years in service = 4 gradient red to white. If years in service >4 solid red.

Thanks for you help!

[xor]

A last attempt.