Strange behavior by MATCH() in array form -- possible Excel bug?

4world

New member
Joined
Jan 22, 2017
Messages
7
Reaction score
0
Points
0
Hello,

Check this out --
AB
1apple= MATCH(FALSE, ISBLANK(A1:A1), 0)
2
Use Ctrl+Shift ENTER to enter the formula in cell B1, whereby it will look like {= MATCH(FALSE, ISBLANK(A1:A1), 0)} on the formula line

It returns #N/A whereas it should return 1. Of course, if you change the range above to A1:A2, then it returns 1.

Is this a bug in Excel?
 
Not really sure of that you trying to achieve. Are you trying check if there is a text in A1?

Try this
=--ISTEXT(A1)

Formula will return 1 or 0
 
Are you trying check if there is a text in A1?

Well, my actual application is quite complex where I'm dynamically generating the range above using OFFSET() along with various other functions. I only narrowed down the problem to this single formula and presented it here in an easy-to-understand format. My actual equation fails due to this 'bug' when the dynamic range comes down to a single cell.

Try this
=--ISTEXT(A1)

This obviously won't work in my app. Thanks for your reply though.
 
Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
https://www.mrexcel.com/forum/excel...expert-help-match-array-form.html#post4736110
 
Pecoflyer,

I deeply respect and appreciate the people who reply to my posts (see my thanks above for an answer which was of no use to me). You've already posted a link to my other post (which did get an excellent answer) so I won't put the link now in the first post.

FYI, I posted something like this on the red link you provided:


1. In reality, cross-linking (providing a URL to the other site) is a good idea since it helps develop a collective community of users and experts.

2. However, the cross-linking rule is quite unusual for several reasons:
a) Numerous websites, on the contrary, prohibit external links to competing websites. So, not only is it unobvious but cross-linking without knowledge can be dangerous.

b) I used to click and read the "Terms & Conditions" of forum websites at subscribe-time long ago but it used to be the same yadi yadi yada (no profanity, no this ..., ... which I didn't engage in any ways) so now I ignore these. This is an unusual rule so you should try to show this on the sign up page (without a link) in a succinct readable message. Else, people who ignore the linked T&C as they do the safety briefing at the start of airlines flights, will not know.

c) It needs to be made public on both (or all) the websites that such and such websites are 'partner' websites so cross-linking is desirable on all such websites. Otherwise, how would I -- an ordinary first-time user -- know whether your request for cross-linking is actually some tactics to promote your own website at the cost of others.

For example, your website post that brought me here is saying I should have put a link to your website on mrexcel.com where I did my other posting. Short of the declaration I suggest above, what guarantees that you are not trying to publicize your own website on mrexcel.com?

Or why can't your request be seen as a subtle assertion of your copyright on the original question? (Btw, my question on the other site was completely re-written and nothing was copied from your site.)

d) I believe most people do a reasonable job of searching the net when they have an Excel problem. It's only when the problem is severe enough (and urgent) that they post on a forum for help. I posted my question on your site yesterday (I.S.T.) and posted on mrexcel.com today after the thread on your site seemed to have died out (as only one useless reply came).

In such cases, I think your admonishing message can be misunderstood. I.e., your message would be much more appropriate had I posted on these two sites almost instantaneously. People can't stop work for days waiting for an answer; even hours is quite long but I had waited over 12-14 hours.

e) Forums see numerous posts per hour so a post can quickly get buried and lost forever (in a matter of one hour). So it is reasonable to expect that a post will get no further responses if none is received in a matter of 4-6 hours (like Amazon says that no answers can be expected to a customer's question on their site if none is received in a couple of days).

Despite the above, I will put a link on the original post on your website in the future BUT NOT on the competing website without their consent or rules (like you suggest).

Thanks for reading this far.
 
c) It needs to be made public on both (or all) the websites that such and such websites are 'partner' websites so cross-linking is desirable on all such websites. Otherwise, how would I -- an ordinary first-time user -- know whether your request for cross-linking is actually some tactics to promote your own website at the cost of others.

It is explicitly stated in the rules for all those sites that cross-posting requires a link, to which you agreed upon signing up for each site. If you did not read the rules, then you are at fault and can expect a moderator on any forum where you have broken those rules to point out the error to you.

Here are the rules for this site:
http://www.excelguru.ca/content.php?184

Cross-posting is not banned, but you are expected to provide links.
 
Last edited:

.... to which you agreed upon signing up for each site. If you did not read the rules, ...

Looks like you did not read my reply either. Please read my point 2(b).

Your suggestion of cross-linking is good but to make people aware quickly (and save you all this bother of telling us), 2(b) will do wonders.
 
Oh, I did read 2b and I was flabbergasted that you consider it OK not to bother to read such things and that you think the rules should change because you don't agree with them. The rules are not going to change, and as long as you are here, you must abide by them. If you wish to take this up with the forum owner, feel free to do so, but do not continue your protest in this thread, please.
 
(as only one useless reply came).

I understand that it can be frustrating waiting for a reply, but I think that this is a most unfortunate way to describe the efforts of someone who is trying to assist you. We have no idea of the competancy of persons using the site and as you provided no background to the source of the problem (as you did later), there is no reason to suppose that the solution suggested was unsuitable.
 
I understand that it can be frustrating waiting for a reply, but I think that this is a most unfortunate way to describe the efforts of someone who is trying to assist you. We have no idea of the competancy of persons using the site and as you provided no background to the source of the problem (as you did later), there is no reason to suppose that the solution suggested was unsuitable.


I totally agree with what you say, and take my words back with a sincere apology. Those words from me were hurtful to the author and I should have been more careful. Thank you for pointing this out Hercules1946

Of course, AliGW has still not understood the gist of my email but I won't go further. Will just stop posting on this forum like she suggests.
 
For the record, this is not true: I have said that you must abide by the rules if you choose to post here, which is quite different.
 
Okay, so here's my take on this. And just to be sure here, I am quoting every line of your reply so that you are aware that I HAVE read your point of view.

1. In reality, cross-linking (providing a URL to the other site) is a good idea since it helps develop a collective community of users and experts.
No one is saying don't. Actually that's not true... I believe OzGrid has a policy of no cross posting ever, but the rest of the forums I believe you'll find do encourage this behaviour. The circle of posters across these forums has a great deal of overlap, and many of us frequent many sites. You'll see this same thing driven from other sites, many with a link back to the article here.

2. However, the cross-linking rule is quite unusual for several reasons:
a) Numerous websites, on the contrary, prohibit external links to competing websites. So, not only is it unobvious but cross-linking without knowledge can be dangerous.
Numerous? The only one I'm aware of is OzGrid. The rest of the forums that I've frequented over the years encourage the behaviour. Granted more may have sprung up over time, but the MVP community and active posters as a whole generally try to mark cross posts for the benefits of others. As I say, the only site I'm aware of that forbids this, is OzGrid.

b) I used to click and read the "Terms & Conditions" of forum websites at subscribe-time long ago but it used to be the same yadi yadi yada (no profanity, no this ..., ... which I didn't engage in any ways) so now I ignore these. This is an unusual rule so you should try to show this on the sign up page (without a link) in a succinct readable message. Else, people who ignore the linked T&C as they do the safety briefing at the start of airlines flights, will not know.
I'm sorry, but I don't think this rule is unusual. I've been active in various communities for almost 15 years, and I wrote that article due to the increasing frustration that I and my colleagues had of people who blatantly didn't care that they were wasting our time.

On the TOC, yes, maybe I could modify the signup page although, to be honest, I highly doubt that will have any effect here at all. The general reality is that people never read the TOC or links. You have no idea how many people email us asking why they can't post when they haven't clicked the link in the activation email. If you read that document, then to be honest, you are actually the one that is a bit unusual. (I commend you for it and WISH that were normal, but it's not.)

c) It needs to be made public on both (or all) the websites that such and such websites are 'partner' websites so cross-linking is desirable on all such websites. Otherwise, how would I -- an ordinary first-time user -- know whether your request for cross-linking is actually some tactics to promote your own website at the cost of others.

For example, your website post that brought me here is saying I should have put a link to your website on mrexcel.com where I did my other posting. Short of the declaration I suggest above, what guarantees that you are not trying to publicize your own website on mrexcel.com?

Or why can't your request be seen as a subtle assertion of your copyright on the original question? (Btw, my question on the other site was completely re-written and nothing was copied from your site.)
Wow... I don't even know what to say to this...

I host a FREE forum to provide help to people, as do these other sites. This is a part of something that the site owners feel is something the world needs, and we provide the forums as a public service. There are exceptions to this (such as Experts Exchange which does have a paid model,) but the article applies to those free sites that are staffed by volunteers.

If you want to pick on MrExcel.com specifically... Bill Jelen (the owner of that site) is the publisher of one of my books. I can guarantee you that he has no issues with back links, and I certainly have no issues with back linking to any other forum from here either. While the linking does help all of us, my primary concern is not wasting the time of the people who volunteer many hours to provide free help and advice to those that come here.

d) I believe most people do a reasonable job of searching the net when they have an Excel problem. It's only when the problem is severe enough (and urgent) that they post on a forum for help. I posted my question on your site yesterday (I.S.T.) and posted on mrexcel.com today after the thread on your site seemed to have died out (as only one useless reply came).
Out of curiosity, how many hours have you spent answering questions that others post? I've spent years doing this, and I honestly cannot agree with this. I'd love to think that this is the case, but I have seen far to many questions posted that have been answered thousands of times, and are very easy to search for. If people did use the search features, we'd have a lot less need for these forums, as the information would be in a few websites alone.

For reference, you do realize that this thread was posted on a Sunday, correct? By far the vast amount of experts on forum posting during their working days, and take their weekends off.

In such cases, I think your admonishing message can be misunderstood. I.e., your message would be much more appropriate had I posted on these two sites almost instantaneously. People can't stop work for days waiting for an answer; even hours is quite long but I had waited over 12-14 hours.
Sorry, but no, I disagree. You are using a volunteer service here. There is no guaranteed SLA on turnaround times, it's based on when volunteers are available and when people can commit their time. By posting in a forum, you accept that. If you want a guaranteed response, then I'd suggest you either go to a forum that provides this (which will carry a fee), or hire a consultant.

I understand that you want help when you want help, and you want an answer right away. I've been there as well, and I know it's frustrating when one is not forthcoming. For me, I posted my question and went to try and help someone else while I waited. That may not be your world, but I have a very hard time with the demand for results in a timely manner for people who are generously donating their family and personal time to help for nothing more than a thank you.

e) Forums see numerous posts per hour so a post can quickly get buried and lost forever (in a matter of one hour). So it is reasonable to expect that a post will get no further responses if none is received in a matter of 4-6 hours (like Amazon says that no answers can be expected to a customer's question on their site if none is received in a couple of days).
Yes, I understand that. Again, no one is saying don't do this. We're simply asking you for the courtesy of posting a link to say "Hey, I've moved the discussion over here", and on the other side saying "I tried posting over here, but got no response." I really fail to understand how this is offensive or unreasonable in any way?

You are making the assumption that the thread is dead an closed after a certain amount of time, and that simply isn't the case. This forum is certainly not as busy as some others, but the experts here generally care and try to help.

Despite the above, I will put a link on the original post on your website in the future BUT NOT on the competing website without their consent or rules (like you suggest).
At the end of the day, I'm primarily concerned about the people posting on THIS site. If you want to irritate the posters on the other sites, that's up to you. But I'd expect to see that other people "admonish" you on those sites the same as they do here.

Good luck in your pursuits.
 
This forum is certainly not as busy as some others, but the experts here generally care and try to help.

Ken
To the main article, I would say 100% spot on.
Regarding the quote Ive extracted, I would just add that less activity can actually help, as unresolved posts are easier to find. Its on the larger sites eg (MrExcel) where the speed of arrival of new posts can take others out of immediate view in a matter of minutes.
 
Hello Ken,

I’m feeling bad that you had to spend so much time responding to my silly post where you have much better things to do, but at the same time gratitude for your having done so. Will try to be brief here and please feel free to not respond to save time.

First: My post had started out acknowledging complete concurrence of the idea of cross-linking (back-linking) so everyplace where you’ve questioned why I would be reluctant to follow the rule or why it’s a desirable thing is somewhat irrelevant. My message only presented to persons like you or the forum owner(s) an ordinary visitor’s PoV so you may also be aware of the other side’s thinking. It also made a suggestion to get this rule across more effectively by putting just this rule in upfront on the signup page (since it’s not immediately obvious to some of us). Granted that my knowledge ought to take a backseat to yours since you have vastly more experience in this field.

I host a FREE forum to provide help to people, as do these other sites. This is a part of something that the site owners feel is something the world needs, and we provide the forums as a public service.

Such actions are striking -- Personally, my deepest regard and admiration for you and for such sites that are selflessly trying to do good for society.

Out of curiosity, how many hours have you spent answering questions that others post?

You are right -- I’m guilty on this account although I did open some questions on your forum in an attempt to first give back. But of course, you must be also be knowing that there are hundreds of other ways to help make this world a better place. Normally I don’t share this info but now that you ask in a challenging way, I’ll say this that I have given perhaps thousands of hours of unpaid time to numerous social causes (anti-war rallies, anti-discrimination by race, religion, ..., poverty alleviation, anti-corruption...) over the past 17 years with not even a thank you expected in return. Even though I’m only an ordinary engineer with no source of income at present except bank interest, I’ve parted with possibly US$100,000 for launching social experiments and supporting other people’s noble work (again nothing in return for me). I’ve voluntarily disowned a small inheritance ($200K) from my parents in favor of donating it for society. It’s my passion too to help others albeit not in your manner. If and when I’m able to publish my book (and if the publisher agrees), I’d love to see it sold at cost price with copyleft and with no requirements to even acknowledge the author. I try although don’t often succeed, to help/give with no name, fame, or anything in return (sometimes anonymously).

For reference, you do realize that this thread was posted on a Sunday, correct?

No, I hadn’t checked the day of the week when I posted. Also, my understanding (incorrect now) was that volunteers respond when they find spare time so that might be greater on the weekends.

Thank you again for the services you have provided on this and other forums that have benefited users like me.

Sincerely with best wishes.
 
Last edited:
Back
Top