How to use the numbers in a formula

Occasional Contributor


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)



11 Replies

Have you tried using one of these functions?



They work just like AVERAGE funtion

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 :)

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

Thanks, but no biscuit, I'm using 2010 and formulatext does not work... :)

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


OK, I have no idea what that is, how to use it, or where to put it :)

Elucidate me

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

   (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))))-

See in attached file

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?



Hi Chris,


Please check attached, I added function and formula, it is 6 in R54


That seems to work, wow, what an effort

Thanks very much

Great, you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies