Tjyylli

Are there continuous functions who are the same in an interval but differ in at least one other point?

Homework question about an engine pulling a train

How to read αἱμύλιος or when to aspirate

Working through the single responsibility principle (SRP) in Python when calls are expensive

What was the last x86 CPU that did not have the x87 floating-point unit built in?

Huge performance difference of the command find with and without using %M option to show permissions

Why doesn't a hydraulic lever violate conservation of energy?

Did the new image of black hole confirm the general theory of relativity?

Is it ok to offer lower paid work as a trial period before negotiating for a full-time job?

How did the audience guess the pentatonic scale in Bobby McFerrin's presentation?

Do working physicists consider Newtonian mechanics to be "falsified"?

One-dimensional Japanese puzzle

Simulating Exploding Dice

Why don't hard Brexiteers insist on a hard border to prevent illegal immigration after Brexit?

Is there a way to generate uniformly distributed points on a sphere from a fixed amount of random real numbers per point?

How to politely respond to generic emails requesting a PhD/job in my lab? Without wasting too much time

Identify 80s or 90s comics with ripped creatures (not dwarves)

Did the UK government pay "millions and millions of dollars" to try to snag Julian Assange?

What happens to a Warlock's expended Spell Slots when they gain a Level?

What's the point in a preamp?

Example of compact Riemannian manifold with only one geodesic.

Can the DM override racial traits?

Are spiders unable to hurt humans, especially very small spiders?

Mortgage adviser recommends a longer term than necessary combined with overpayments

Counting the Cells in a Named Range without VBA

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

3

I have a disjoint set of 9 cells: A1,B3,C5,D7,E11,F13,G17,H19,I23. I have assigned a Name to these cells: MyPicks

I have a simple formula to pick the value in one of these cells at random:

=SMALL(MyPicks,RANDBETWEEN(1,9))

the formula works:

However each time I change the number of cells in the Named Range, I must go back and change the 9 in the formula! So I decided to "fix" the formula:

First try:

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)))

This appeared to work. However COUNTA() only counts cells with values or Nulls. It ignores totally empty cells. So...........

Second try:

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)+COUNTBLANK(MyPicks)))

Sadly, this produces a #VALUE! error because COUNTBLANK() does not work on disjoint ranges. So............

Third try:

I created a small VBA UDF:

Public Function nCount(r As Range) As Long

    nCount = r.Count

End Function

Using this UDF solved the problem. I then discovered my customer was macrophobic and the solution was instantly rejected.

Is it possible to count the total number of cells in a Named Range without VBA ??

microsoft-excel worksheet-function

share|improve this question

edited yesterday

Gary's Student

asked Apr 1 '15 at 14:19

Gary's StudentGary's Student

14.2k31833

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +1 Excellent question. We should get meta to point to this as an example of what to include so that your data setup, problem, and previous attempts are all clearly understood.
  
  – Engineer Toast
  Apr 1 '15 at 14:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The question I was thinking of was actually yours. Nevermind
  
  – Raystafarian
  Apr 1 '15 at 14:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why count the number of cells? If you do that then the SMALL function can return an error because RANDBETWEEN can return a number larger than the count of numbers in MyPicks
  
  – barry houdini
  Apr 1 '15 at 19:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @barryhoudini OUCH ..................you are correct!! .............I will have to pick from a table of addresses of the Named Range ................can you suggest another approach ??
  
  – Gary's Student
  Apr 1 '15 at 19:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not sure what you mean - text values won't work in this context because SMALL only deals with numbers. Do you want to pick random text values from a discontiguous range? That might be tricky......
  
  – barry houdini
  Apr 1 '15 at 20:58
  

  
  
  
  

 | 
show 1 more comment

3

I have a disjoint set of 9 cells: A1,B3,C5,D7,E11,F13,G17,H19,I23. I have assigned a Name to these cells: MyPicks

I have a simple formula to pick the value in one of these cells at random:

=SMALL(MyPicks,RANDBETWEEN(1,9))

the formula works:

However each time I change the number of cells in the Named Range, I must go back and change the 9 in the formula! So I decided to "fix" the formula:

First try:

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)))

This appeared to work. However COUNTA() only counts cells with values or Nulls. It ignores totally empty cells. So...........

Second try:

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)+COUNTBLANK(MyPicks)))

Sadly, this produces a #VALUE! error because COUNTBLANK() does not work on disjoint ranges. So............

Third try:

I created a small VBA UDF:

Public Function nCount(r As Range) As Long

    nCount = r.Count

End Function

Using this UDF solved the problem. I then discovered my customer was macrophobic and the solution was instantly rejected.

Is it possible to count the total number of cells in a Named Range without VBA ??

microsoft-excel worksheet-function

share|improve this question

edited yesterday

Gary's Student

asked Apr 1 '15 at 14:19

Gary's StudentGary's Student

14.2k31833

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +1 Excellent question. We should get meta to point to this as an example of what to include so that your data setup, problem, and previous attempts are all clearly understood.
  
  – Engineer Toast
  Apr 1 '15 at 14:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The question I was thinking of was actually yours. Nevermind
  
  – Raystafarian
  Apr 1 '15 at 14:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why count the number of cells? If you do that then the SMALL function can return an error because RANDBETWEEN can return a number larger than the count of numbers in MyPicks
  
  – barry houdini
  Apr 1 '15 at 19:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @barryhoudini OUCH ..................you are correct!! .............I will have to pick from a table of addresses of the Named Range ................can you suggest another approach ??
  
  – Gary's Student
  Apr 1 '15 at 19:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not sure what you mean - text values won't work in this context because SMALL only deals with numbers. Do you want to pick random text values from a discontiguous range? That might be tricky......
  
  – barry houdini
  Apr 1 '15 at 20:58
  

  
  
  
  

 | 
show 1 more comment

I have a disjoint set of 9 cells: A1,B3,C5,D7,E11,F13,G17,H19,I23. I have assigned a Name to these cells: MyPicks

I have a simple formula to pick the value in one of these cells at random:

=SMALL(MyPicks,RANDBETWEEN(1,9))

the formula works:

However each time I change the number of cells in the Named Range, I must go back and change the 9 in the formula! So I decided to "fix" the formula:

First try:

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)))

This appeared to work. However COUNTA() only counts cells with values or Nulls. It ignores totally empty cells. So...........

Second try:

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)+COUNTBLANK(MyPicks)))

Sadly, this produces a #VALUE! error because COUNTBLANK() does not work on disjoint ranges. So............

Third try:

I created a small VBA UDF:

Public Function nCount(r As Range) As Long

    nCount = r.Count

End Function

Using this UDF solved the problem. I then discovered my customer was macrophobic and the solution was instantly rejected.

Is it possible to count the total number of cells in a Named Range without VBA ??

microsoft-excel worksheet-function

share|improve this question

edited yesterday

Gary's Student

asked Apr 1 '15 at 14:19

Gary's StudentGary's Student

14.2k31833

=SMALL(MyPicks,RANDBETWEEN(1,9))

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)))

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)+COUNTBLANK(MyPicks)))

Public Function nCount(r As Range) As Long

    nCount = r.Count

End Function

share|improve this question

edited yesterday

Gary's Student

asked Apr 1 '15 at 14:19

Gary's StudentGary's Student

14.2k31833

share|improve this question

edited yesterday

Gary's Student

asked Apr 1 '15 at 14:19

Gary's StudentGary's Student

14.2k31833

asked Apr 1 '15 at 14:19

Gary's StudentGary's Student

14.2k31833

asked Apr 1 '15 at 14:19

Gary's StudentGary's Student

14.2k31833

2 Answers
2

active

oldest

votes

2

How about this little beauty:

=SUM(FREQUENCY(MyPicks,MyPicks))

Therefore, your whole formula would be:

=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))

---

EDIT: As pointed out by barry houdini, the SMALL function will only return numeric values. In that case, the SUM(FREQUENCY()) solution is overkill. Instead, simply use

=SMALL(MyPicks,RANDBETWEEN(1,COUNT(MyPicks)))

share|improve this answer

edited Apr 1 '15 at 20:07

answered Apr 1 '15 at 14:56

Engineer ToastEngineer Toast

2,9381828

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks!.................it saddens me that I can only up-vote this once!
  
  – Gary's Student
  Apr 1 '15 at 15:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Does this really do what you asked? Doesn't =SUM(FREQUENCY(MyPicks,MyPicks)) just give a count of numbers in MyPicks - you can get the same result using =COUNT(MyPicks)
  
  – barry houdini
  Apr 1 '15 at 19:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @barryhoudini COUNT only counts numbers which, given the exact example, would meet OP's needs. My solution works for numbers or text.
  
  – Engineer Toast
  Apr 1 '15 at 20:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  From Excel Help: FREQUENCY ignores blanks cells and text.....
  
  – barry houdini
  Apr 1 '15 at 20:56
  

  
  
  
  

add a comment | 

1

If each of the disjoint ranges is comprised of a single cell, the following formula will return the number of cells in the range, no matter what the content, or even if they are blank, which I think is what you want:

=LEN(CELL("address",MyPicks))-LEN(SUBSTITUTE(CELL("address",MyPicks),",",""))+1

share|improve this answer

answered Apr 4 '15 at 17:55

Ron RosenfeldRon Rosenfeld

2,1092611

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This works great for ranges in which no two cells share a common border. Thanks ..................having empty cells ruins the approach of using COUTNA() which is unfortunate since COUNTA() works whether the range is disjoint or "joint".
  
  – Gary's Student
  Apr 4 '15 at 18:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It might be possible to do something with "joint" cells, but I think it will be easier to cure your customer of his macrophobia :-)
  
  – Ron Rosenfeld
  Apr 4 '15 at 18:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ..........................Amen!
  
  – Gary's Student
  Apr 4 '15 at 18:25
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f896701%2fcounting-the-cells-in-a-named-range-without-vba%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

How about this little beauty:

=SUM(FREQUENCY(MyPicks,MyPicks))

Therefore, your whole formula would be:

=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))

---

EDIT: As pointed out by barry houdini, the SMALL function will only return numeric values. In that case, the SUM(FREQUENCY()) solution is overkill. Instead, simply use

=SMALL(MyPicks,RANDBETWEEN(1,COUNT(MyPicks)))

share|improve this answer

edited Apr 1 '15 at 20:07

answered Apr 1 '15 at 14:56

Engineer ToastEngineer Toast

2,9381828

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks!.................it saddens me that I can only up-vote this once!
  
  – Gary's Student
  Apr 1 '15 at 15:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Does this really do what you asked? Doesn't =SUM(FREQUENCY(MyPicks,MyPicks)) just give a count of numbers in MyPicks - you can get the same result using =COUNT(MyPicks)
  
  – barry houdini
  Apr 1 '15 at 19:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @barryhoudini COUNT only counts numbers which, given the exact example, would meet OP's needs. My solution works for numbers or text.
  
  – Engineer Toast
  Apr 1 '15 at 20:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  From Excel Help: FREQUENCY ignores blanks cells and text.....
  
  – barry houdini
  Apr 1 '15 at 20:56
  

  
  
  
  

add a comment | 

2

How about this little beauty:

=SUM(FREQUENCY(MyPicks,MyPicks))

Therefore, your whole formula would be:

=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))

---

EDIT: As pointed out by barry houdini, the SMALL function will only return numeric values. In that case, the SUM(FREQUENCY()) solution is overkill. Instead, simply use

=SMALL(MyPicks,RANDBETWEEN(1,COUNT(MyPicks)))

share|improve this answer

edited Apr 1 '15 at 20:07

answered Apr 1 '15 at 14:56

Engineer ToastEngineer Toast

2,9381828

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks!.................it saddens me that I can only up-vote this once!
  
  – Gary's Student
  Apr 1 '15 at 15:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Does this really do what you asked? Doesn't =SUM(FREQUENCY(MyPicks,MyPicks)) just give a count of numbers in MyPicks - you can get the same result using =COUNT(MyPicks)
  
  – barry houdini
  Apr 1 '15 at 19:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @barryhoudini COUNT only counts numbers which, given the exact example, would meet OP's needs. My solution works for numbers or text.
  
  – Engineer Toast
  Apr 1 '15 at 20:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  From Excel Help: FREQUENCY ignores blanks cells and text.....
  
  – barry houdini
  Apr 1 '15 at 20:56
  

  
  
  
  

add a comment | 

How about this little beauty:

=SUM(FREQUENCY(MyPicks,MyPicks))

Therefore, your whole formula would be:

=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))

---

EDIT: As pointed out by barry houdini, the SMALL function will only return numeric values. In that case, the SUM(FREQUENCY()) solution is overkill. Instead, simply use

=SMALL(MyPicks,RANDBETWEEN(1,COUNT(MyPicks)))

share|improve this answer

edited Apr 1 '15 at 20:07

answered Apr 1 '15 at 14:56

Engineer ToastEngineer Toast

2,9381828

=SUM(FREQUENCY(MyPicks,MyPicks))

=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))

=SMALL(MyPicks,RANDBETWEEN(1,COUNT(MyPicks)))

share|improve this answer

edited Apr 1 '15 at 20:07

answered Apr 1 '15 at 14:56

Engineer ToastEngineer Toast

2,9381828

answered Apr 1 '15 at 14:56

Engineer ToastEngineer Toast

2,9381828

answered Apr 1 '15 at 14:56

Engineer ToastEngineer Toast

2,9381828

1

If each of the disjoint ranges is comprised of a single cell, the following formula will return the number of cells in the range, no matter what the content, or even if they are blank, which I think is what you want:

=LEN(CELL("address",MyPicks))-LEN(SUBSTITUTE(CELL("address",MyPicks),",",""))+1

share|improve this answer

answered Apr 4 '15 at 17:55

Ron RosenfeldRon Rosenfeld

2,1092611

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This works great for ranges in which no two cells share a common border. Thanks ..................having empty cells ruins the approach of using COUTNA() which is unfortunate since COUNTA() works whether the range is disjoint or "joint".
  
  – Gary's Student
  Apr 4 '15 at 18:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It might be possible to do something with "joint" cells, but I think it will be easier to cure your customer of his macrophobia :-)
  
  – Ron Rosenfeld
  Apr 4 '15 at 18:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ..........................Amen!
  
  – Gary's Student
  Apr 4 '15 at 18:25
  

  
  
  
  

add a comment | 

1

If each of the disjoint ranges is comprised of a single cell, the following formula will return the number of cells in the range, no matter what the content, or even if they are blank, which I think is what you want:

=LEN(CELL("address",MyPicks))-LEN(SUBSTITUTE(CELL("address",MyPicks),",",""))+1

share|improve this answer

answered Apr 4 '15 at 17:55

Ron RosenfeldRon Rosenfeld

2,1092611

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This works great for ranges in which no two cells share a common border. Thanks ..................having empty cells ruins the approach of using COUTNA() which is unfortunate since COUNTA() works whether the range is disjoint or "joint".
  
  – Gary's Student
  Apr 4 '15 at 18:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It might be possible to do something with "joint" cells, but I think it will be easier to cure your customer of his macrophobia :-)
  
  – Ron Rosenfeld
  Apr 4 '15 at 18:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ..........................Amen!
  
  – Gary's Student
  Apr 4 '15 at 18:25
  

  
  
  
  

add a comment | 

If each of the disjoint ranges is comprised of a single cell, the following formula will return the number of cells in the range, no matter what the content, or even if they are blank, which I think is what you want:

=LEN(CELL("address",MyPicks))-LEN(SUBSTITUTE(CELL("address",MyPicks),",",""))+1

share|improve this answer

answered Apr 4 '15 at 17:55

Ron RosenfeldRon Rosenfeld

2,1092611

=LEN(CELL("address",MyPicks))-LEN(SUBSTITUTE(CELL("address",MyPicks),",",""))+1

share|improve this answer

answered Apr 4 '15 at 17:55

Ron RosenfeldRon Rosenfeld

2,1092611

answered Apr 4 '15 at 17:55

Ron RosenfeldRon Rosenfeld

2,1092611

answered Apr 4 '15 at 17:55

Ron RosenfeldRon Rosenfeld

2,1092611