SOLVED

How to use the numbers in a formula

Copper Contributor

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 

11 Replies

Have you tried using one of these functions?

COUNT

COUNTA

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

image.png

Here Insert-Module and copy paste function code here

image.png

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

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

best response confirmed by CHRIS KAWIK (Copper Contributor)
Solution

Hi Chris,

 

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

Hi,

That seems to work, wow, what an effort

Thanks very much

Great, you are welcome

1 best response

Accepted Solutions
best response confirmed by CHRIS KAWIK (Copper Contributor)
Solution

Hi Chris,

 

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

View solution in original post