You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Is your feature request related to a problem? Please describe.
I would like to highlight cells in a grid of data that fall outside certain boundary conditions.
For example, I have a running list of electricity costs and bills from my utility that spans most of a decade. Each month there's a bill that lists a date range it covers (a variable number of days), a total cost, the number of kWh used, a 3 tiered cost per kWh of usage (which varies month-to-month), and some extra fees. I populate an expected average kWh per 30-days and a maximum cost variance as constants in cells at the top of my file.
For each bill I do calculations to verify I get the same result as the overall bill total, which I'd like to highlight if it mismatches, and I do calculations to see how much my actual costs vary from a theoretical expected usage over the same actual billing time period, which I'd like to highlight as well if they're larger or smaller than the predefined variance limit.
Describe the solution you'd like
The ability to supply a formula resulting in a logic value (TRUE or FALSE), a cell formatting (background and/or text color), and a range of cells to apply it to, that will have the formula applied cell-by-cell in the range and change the formatting to the pre-defined one if the logic result is TRUE. The formula would need to be able to support both relative and absolute cell references.
Alternatively, provide a function that can be used in the formula of cells to change the cell's text and/or background color. This would make it much clearer what formatting logic is being applied to each cell, keep the per-cell calculations all defined in one place, and allow use of the Fill function for extending the formatting over arbitrary cells. It would make functions with conditional formatting logic much more complicated though, and it would be necessary to allow the function to be called as part of the in-cell formula without affecting output calculations.
Describe alternatives you've considered
The full Excel or GSuite Sheets behavior for conditional formatting.
It's mostly overkill, and the interface gets difficult very quickly. Most people don't need it.
Additional context
The text was updated successfully, but these errors were encountered:
Have you read the Longevity Statement?
Yes
Is your feature request related to a problem? Please describe.
I would like to highlight cells in a grid of data that fall outside certain boundary conditions.
For example, I have a running list of electricity costs and bills from my utility that spans most of a decade. Each month there's a bill that lists a date range it covers (a variable number of days), a total cost, the number of kWh used, a 3 tiered cost per kWh of usage (which varies month-to-month), and some extra fees. I populate an expected average kWh per 30-days and a maximum cost variance as constants in cells at the top of my file.
For each bill I do calculations to verify I get the same result as the overall bill total, which I'd like to highlight if it mismatches, and I do calculations to see how much my actual costs vary from a theoretical expected usage over the same actual billing time period, which I'd like to highlight as well if they're larger or smaller than the predefined variance limit.
Describe the solution you'd like
The ability to supply a formula resulting in a logic value (TRUE or FALSE), a cell formatting (background and/or text color), and a range of cells to apply it to, that will have the formula applied cell-by-cell in the range and change the formatting to the pre-defined one if the logic result is TRUE. The formula would need to be able to support both relative and absolute cell references.
Alternatively, provide a function that can be used in the formula of cells to change the cell's text and/or background color. This would make it much clearer what formatting logic is being applied to each cell, keep the per-cell calculations all defined in one place, and allow use of the Fill function for extending the formatting over arbitrary cells. It would make functions with conditional formatting logic much more complicated though, and it would be necessary to allow the function to be called as part of the in-cell formula without affecting output calculations.
Describe alternatives you've considered
Additional context
The text was updated successfully, but these errors were encountered: