SOLVED

# How to use the numbers in a formula

Highlighted
Occasional Contributor

# 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 many cells are being summed

I think I'm using excel 2010 (Don't want macros or VBA if possible)

Thanks

11 Replies
Highlighted

# Re: How to use the numbers in a formula

Have you tried using one of these functions?

COUNT

COUNTA

They work just like AVERAGE funtion

Highlighted

# Re: How to use the numbers in a formula

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

Highlighted

# Re: How to use the numbers in a formula

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

# Re: How to use the numbers in a formula

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

Highlighted

# Re: How to use the numbers in a formula

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

Highlighted

# Re: How to use the numbers in a formula

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

Elucidate me

Highlighted

# Re: How to use the numbers in a formula

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

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

Highlighted

# Re: How to use the numbers in a formula

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

Highlighted
Solution

# Re: How to use the numbers in a formula

Hi Chris,

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

Highlighted

# Re: How to use the numbers in a formula

Hi,

That seems to work, wow, what an effort

Thanks very much

Highlighted

# Re: How to use the numbers in a formula

Great, you are welcome