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.