Forum Discussion

CHRIS KAWIK's avatar
CHRIS KAWIK
Copper Contributor
Jan 30, 2019
Solved

How to use the numbers in a formula

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 ma...
excel
Formulas and Functions
Ayman_Hassan's avatar
Ayman_Hassan
MCT
Jan 30, 2019

Have you tried using one of these functions?

COUNT

COUNTA

They work just like AVERAGE funtion

CHRIS KAWIK's avatar
CHRIS KAWIK
Copper Contributor
Jan 30, 2019

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 30, 2019

    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
    • CHRIS KAWIK's avatar
      CHRIS KAWIK
      Copper Contributor
      Jan 31, 2019

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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 31, 2019

        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

         

Resources