#1
|
|||
|
|||
Average on conditional formatted cells
Hi all
I need some help I've made some rules with conditional formatting on a spreadsheet Like when true - mark as bold or mark with a blue background. Now I would like to do some calculations based on those rules. For example, I need to calculate the average of all bolded cells (and only the bolded cells) Can it be done and how? |
#2
|
|||
|
|||
It's simple! Use rules you used for conditional formatting as filter conditions for average calculations.
The easies wayt will be to use (some) hidden column(s), where some numeric values are calculated depending on one specific conditional formatting rule, or on set of conditional formatting rules. And then you can use hidden column(s) to define conditional formatting rules too - like when value in hidden column is x1, then rule 1 is applied. |
#3
|
||||
|
||||
The Moderator never gets tired of advising all OPs to attach a sample sheet on which others could base their replies and/or suggestions instead of everyone making their own hypothetical data. If I got Arvi's reply right, attached is a sample sheet for the OP to work around. Hide column B.
|
#4
|
|||
|
|||
Thanks to both of you
My problem is that my criteria are on one column but the data that I need to do the average on based on that criteria is on another column How can I do it? |
#5
|
|||
|
|||
Code:
=SUMIFS(ValuesColumn, CriteriaColumn, CriteriaValue)/COUNTIFS(CriteriaColumn, CriteriaValue) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to highlight the cells using conditional formatting? | LearnerExcel | Excel | 1 | 01-01-2019 01:43 AM |
Conditional formating all cells in an array based on adjacent cells | deejay | Excel | 6 | 12-20-2016 12:00 PM |
Dynamic, Formatted Cells (Linked) | ShankedS | Excel | 1 | 08-11-2015 02:34 AM |
Header must toggle text & color + show count of conditionally formatted cells below | Franktoon | Excel | 3 | 02-18-2014 02:10 PM |
Odd behaviour in formatted cells | furface00 | Excel | 3 | 03-11-2011 08:02 AM |