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

       

      • CHRIS KAWIK's avatar
        CHRIS KAWIK
        Copper Contributor
        Jan 31, 2019

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

        Elucidate me

Resources