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

Resources