Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 414K Members
- 8,212 Online
- 477K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- How to use the numbers in a formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
## How to use the numbers in a formula

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019 03:18 AM

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

Labels:

11 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019 05:47 AM

Have you tried using one of these functions?

COUNT

COUNTA

They work just like AVERAGE funtion

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019 08:52 AM - edited 01-30-2019 08:53 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019 10:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019 01:17 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019 02:27 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019 02:54 AM

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

Elucidate me

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019 04:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019 06:07 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019 10:17 AM

SolutionHi Chris,

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

Best Response confirmed by
CHRIS KAWIK (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-01-2019 01:50 AM

Hi,

That seems to work, wow, what an effort

Thanks very much

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
28.9K
Views

1 Likes

35 Replies

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
28.1K
Views

11 Likes

14 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
11.4K
Views

0 Likes

9 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
142K
Views

6 Likes

28 Replies

W10-1903 UNC path failing 0x80070043

kurgan
in
Windows Insider Program
on
03-22-2019
23.4K
Views

1 Likes

80 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft