Average Based on Font Colour

[Evans8773]

Hi all

I would like to know if its possible to get an average on a range on cells that contain black numbers only. In the screenshot attached i have an average between cells D6:G6...Ideally i want it to be D6:M6 but when i use that it averages the scores over the 10 weeks and its lower than it should because be it is obviously counting data that will eventually be auto filled in as the weeks pass but is currently 0.

So is it possible to have an average formula that would work it out based on the black font and ignore the grey font until i change it to black as the weeks progress.

Thanks in advance

 

[p45cal]

It's not easy for a formula to detect font colour - it may be possible via a vba function, but there's probably an easier solution; what's putting those zeroes in the grey cells? If it's a formula then we should be able tweak that formula to show a blank instead of a zero, then the average formula will be straightforward.
Will all the rows always have equal numbers of greyed cells at a given time? If so, is there a value or values somewhere in the workbook that can be used to derive how many weeks to average?
How are those cells being made grey or black? Is it a manual process? or is it by conditional formatting? If it's the latter then we can use the same formula as is in the conditional formatting to determoine what to average.

What version of Excel are you using (I don't recognise your Treadstone7*)?
It's better to attach a workbook rather than a picture of one.

 

[Evans8773]

So its a football competition you have to guess the 3 scores each week...so in this w4.png column p total points scored for this week and the scores auto populate into those grey zeros which i then manually turn black. Only grey because because they are have not been used yet. At the moment i just alter adjust the average formula to include the new columns. Hope this helps

I am using Microsoft 365 apps for enterprise

Cannot upload workbook says its too large ??

 

[p45cal]

and the scores auto populate into those grey zero

So how do they auto-populate? Is it a macro, a formula, something else?
What I'm saying, is that whatever currently puts the zeroes there, can be tweaked to put a blank for unused. Then it's dead easy to have a single, static, formula to give you the average.

 

[Evans8773]

the weekly points total auto populates with the following formula using week 10 & P17 cell as an example ='W10'!P17

 

[p45cal]

OK, so what populates 'W10'!P17 ?!
If the file's too big to share, try saving as .xlsb , it might be smaller. Otherwise share it via a link on some file-sharing site.
If I have to, I'll write a UDF to do the averaging, but I think you shouldn't have to do any manual colouring (use CF) and any averaging should be automatic.

 

[Evans8773]

(SEE SS1) TOTAL POINTS FOR WEEK 10 IN CELL P15 (N15+P15)
P15 THEN FORMULA =W10!P15 POPULATES THE 12 POINTS INTO LEAGUE TABLE CELL M11

DROPBOX LINK FOR WORKBOOK

FULLY FORMULATED.xlsm

Shared with Dropbox

www.dropbox.com

 

[p45cal]

On row 34 of the weekly sheets you have Official Final Scores in column C to J. I'm exploring the possibility of using these to signify which weeks need averaging on the Table sheet. I see there are all 7s in many of them; why is that and what does it signify?
Also, when final scores are entered there, will all 6 cells always have values entered (will there ever be a case when only some of these 6 cells on a sheet will have real scores in?)?

 

[Evans8773]

ss1 screenshot. Each team scored 7 goals c34-j34
Dean Richards predicted each team to score 7 goals so his correctly predict goals turns blue.

So if you were to delete those 7's (c34-j34) every square above in the range would turn blue if 0 or the 7's are deleted. And formula would count those blue squares as points

So next the next week is w5 of 10 if you where to leave cells with 7's blank the formula counts that as a 0 and as the coming weeks have no guessed scores in the formula counts every empty square as points

Hope that makes it a bit clearer ?

 

[Evans8773]

video in drobox of what i mean

https://www.dropbox.com/scl/fi/3wwk62i5z0lzw6chktxfe/vid1.mp4?rlkey=1ae9tuqfwks4a652k28urhdm9&dl=0

Cheers

 

[p45cal]

In the linked-to file: https://app.box.com/s/9e14go0k5h6aipef84angpx0jsmf9mqr
Since I think you have a sufficiently up to date version of Excel there's a formula in cell C6 and below which should give you your averages.
If I'm wrong, there's another formula in cell AA6 which you should be able to use.
It's important to realise that these formulae don't update themselves on a change of font colour, which means you'll have to force the sheet to recalculate, either with F9 on the keyboard or using the menus: Calculsate Sheet in the Calculation section of the Formulas tab of the ribbon.