Jan 30 2019 03:18 AM
Hi
I have the formula =AVERAGE(P230:P235) which works.
I'd like to be able to apply a conditional format to show that the range is 6 cells
or have another cell that shows the number 6, i.e. how many cells are being summed
I think I'm using excel 2010 (Don't want macros or VBA if possible)
Thanks
Jan 30 2019 05:47 AM
Have you tried using one of these functions?
COUNT
COUNTA
They work just like AVERAGE funtion
Jan 30 2019 08:52 AM - edited Jan 30 2019 08:53 AM
Nope, wrong kind of thing. I want it to show that there are six cells being summed.
I'm afraid there is no biscuit for that :)
Jan 30 2019 10:11 AM
Hi Chris,
It could be
=MID(FORMULATEXT(A1),(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(FORMULATEXT(A1), LEN(FORMULATEXT(A1))-SEARCH(":",FORMULATEXT(A1)))&"0123456789"))+SEARCH(":",FORMULATEXT(A1))),LEN(FORMULATEXT(A1))-(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(FORMULATEXT(A1), LEN(FORMULATEXT(A1))-SEARCH(":",FORMULATEXT(A1)))&"0123456789"))+SEARCH(":",FORMULATEXT(A1))))-MID(FORMULATEXT(A1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},FORMULATEXT(A1)&"0123456789")),SEARCH(":",FORMULATEXT(A1))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C1&"0123456789")))+1=6
Jan 31 2019 01:17 AM
Thanks, but no biscuit, I'm using 2010 and formulatext does not work... :)
Jan 31 2019 02:27 AM
I see. That could be workaround with UDF for FORMULATEXT like
Public Function MyFormula(r As Range) As String MyFormula = r(1).Formula End Function
Jan 31 2019 02:54 AM
OK, I have no idea what that is, how to use it, or where to put it :)
Elucidate me
Jan 31 2019 04:11 AM
Okay, perhaps in 2010 it's bit different, you shall first open VBA. Right click on sheet tab and View Code then
Here Insert-Module and copy paste function code here
Save your file as macro-enabled workbook
Use formula to calculate number of cells with above function
=MID(MyFormula(A1), (MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(MyFormula(A1), LEN(MyFormula(A1))-SEARCH(":",MyFormula(A1)))&"0123456789"))+SEARCH(":",MyFormula(A1))), LEN(MyFormula(A1))-(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(MyFormula(A1), LEN(MyFormula(A1))-SEARCH(":",MyFormula(A1)))&"0123456789"))+SEARCH(":",MyFormula(A1))))- MID(MyFormula(A1), MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MyFormula(A1)&"0123456789")), SEARCH(":",MyFormula(A1))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MyFormula(A1)&"0123456789"))) +1
See in attached file
Jan 31 2019 06:07 AM
OK thanks for answering again, I tried that, but get #name?
I've attached part of my sheet. (Hopefully)
Col M (Gold cell) takes the average of the previous 6 results in Col N (Blue cells)
I would like to see the number 6 in Col R (Yellow cell)
What happens is, if someone else uses the sheet they insert/delete rows and so the average in Col M becomes more or less than 6, I'd like to see the number 6 so that I know if they have fiddled with it.
so M54 (=AVERAGE(N49:N54)) is the average of 6 cells N49 to N54...
Does that make sense?
Thanks
Jan 31 2019 10:17 AM
SolutionHi Chris,
Please check attached, I added function and formula, it is 6 in R54
Feb 01 2019 01:50 AM
Hi,
That seems to work, wow, what an effort
Thanks very much
Jan 31 2019 10:17 AM
SolutionHi Chris,
Please check attached, I added function and formula, it is 6 in R54