- 546K Members
- 2,474 Online
- 652K Conversations

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

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

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

Highlighted

- 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

Highlighted

- 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

Highlighted

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

Highlighted

- 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

Highlighted

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

Highlighted

- 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

Highlighted

- 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

Highlighted

- 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

Highlighted

- 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

Highlighted

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

Highlighted

- 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

Highlighted

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

Related Conversations

Outlook Message box GUI blacked out when sending attachment through Excel

midohioboarder
in
Office 365
on
01-24-2020
235
Views

0 Likes

0 Replies

A problem with the Zoom level of a Tab

Tavory
in
Discussions
on
11-07-2019
372
Views

0 Likes

9 Replies

Pivot Table StDev calculates different value then the STDEV formula

zsoltturkosi
in
Excel
on
10-21-2019
206
Views

0 Likes

4 Replies

PWA fullscreen like IE11 kiosk mode

rogihee
in
Discussions
on
10-19-2019
446
Views

0 Likes

5 Replies

Trying to add multiple users to distribution group and getting error: Cannot validate argument

Test SharePoint
in
Office 365
on
10-12-2019
872
Views

0 Likes

4 Replies

need macro to find and copy range of data between specified cells.

spike3rd
in
Office 365
on
10-09-2019
164
Views

0 Likes

0 Replies

Share

Popular

Learning Resources

Programs

Values

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