Hi,
I need a formula to calculate the average of a column of cells depending on the fill colour. For example column A as 20 rows of data, 5 of these are marked red. I need to find the average of these numbers and ignore the rest.
I also need to do the same but for standard deviation, max value, min value and count. I had thought about trying to use colorfunction but im not sure how it could be combined with average etc. Is there an alternative?
Last edited by JONNY981; 06-09-2011 at 06:48 AM.
Using Excel 2003 this is not possible without VBA
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Ok, thanks.
Can you tell me how exactly to do it with VBA please?
You might find this link useful
Chip Pearson - Color Functions In Excel
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Or this one:
Put into cell C2
=averagered(A1:A20;3)
or
=averagered(A1:A20,3)
Put into a macromodule:
or
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by snb; 06-06-2011 at 09:20 AM.
thanks, although I tried this and excel is telling me that the formula contains an error. any ideas why?
Try changing the delimiter to a comma
e.g.
=averagered(A1:A20,3)
Here's an example
ColourFunction(range with colour,range to calculate,option:average)
Example of use, see attachment
=ColourFunction($A$2,$A$2:$A$4,TRUE)
A2 has a fill colour to calculate with
A2:A4 is the range to average
True is optional, if missed defaults to SUM
Thanks.
I changed the delimiter to a comma but then i got a #VALUE! error in the cell even though I am referencing the correct cells.
The colourfunction option seems good, although can I use this for min, max and standard deviation too?
You can adapt the Function by using different worksheet Functions. I have a more complex version at home, I'll try to post it later
See the attachment.
I've added some options to the Function
Thanks for the help everyone. The colourfunction option seems to work the best for me.
I know maybe this is slightly off the main subject but some of my data will have no red cells at all, and instead of a #VALUE! error I would like the cell to say 'No Breaks'. I attempted to write an if statement but I think im a bit off the mark...
Please Login or Register to view this content.
Use ISERROR.
Ok thanks again!
Hi royUK-I tried to download the attachment file and the formulas gave me a syntax error of #NAME?. Is this because I'm on a US version of Excel 2010? Any guidance would be greatly appreciated.Originally Posted by royUK
Jonny,
You ran in the the same issue that I have. When there are no cells colored I would like to have returned "0" not #value.
Is there a way to do this right in the VBA code for the ColourFunction?
Currently I add an extra colored cell and then subtract "1".
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks