Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-09-2019, 04:35 AM
nadavmaroko nadavmaroko is offline Average on conditional formatted cells Windows 10 Average on conditional formatted cells Office 2010
Novice
Average on conditional formatted cells
 
Join Date: Feb 2019
Posts: 2
nadavmaroko is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 02-09-2019, 07:07 AM
ArviLaanemets ArviLaanemets is offline Average on conditional formatted cells Windows 8 Average on conditional formatted cells Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #3  
Old 02-09-2019, 11:23 PM
Marcia's Avatar
Marcia Marcia is offline Average on conditional formatted cells Windows 7 32bit Average on conditional formatted cells Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Attached Files
File Type: xlsx Average.xlsx (9.7 KB, 8 views)
Reply With Quote
  #4  
Old 02-10-2019, 02:42 AM
nadavmaroko nadavmaroko is offline Average on conditional formatted cells Windows 10 Average on conditional formatted cells Office 2010
Novice
Average on conditional formatted cells
 
Join Date: Feb 2019
Posts: 2
nadavmaroko is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 02-10-2019, 04:13 AM
ArviLaanemets ArviLaanemets is offline Average on conditional formatted cells Windows 8 Average on conditional formatted cells Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Code:
=SUMIFS(ValuesColumn, CriteriaColumn, CriteriaValue)/COUNTIFS(CriteriaColumn, CriteriaValue)
In case you have several criteria columns, you add additional ",CriteriaColumn, CriteriaValue" pairs into both SUMIFS() and COUNTIFS() part of formula.
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:26 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft