Problems with Excel formulas after update

Question

I am an IT Trainer for a community college and taught a level 2 Excel class for almost 4 years.  I have tried reporting it on a different site per my manager, but nothing came of it and just found this site so I'm reporting it here.

After an update, I am now getting errors when using the and and or commands when using named ranges in my formulas.  I have included pictures to explain the problem, both the incorrect result where I used ranges as well as the correct result where I used the individual cells. I am also including an example where the named ranges worked correctly in a formula.

This is the first build that I noticed it in as well as my current build where I'm still dealing with the error.

Current build:

This shows the ranges working correctly.

This is the AND command, with the ranges 1st and the individual cells 2nd.

Here is the OR command with the error using the named range 1st and no errors 2nd.

Answer 1

Hi Tami,

I read the post and based on your previous conversation, I tested on my with the latest version of Current Channel (Preview) i.e. 2007 (Build 13029.20236) and I can use range in the mentioned functions (AND and OR) without any trouble.

Here’s a screenshot for your reference.

As your Office build is behind an update, please try updating Office to the latest version and check if you still get the same problem (your build is13029.20200 and the latest is 13029.20236). To know how to install updates, see Install Office updates

If the issue persists after updating Office, please provide us with your sample workbook, so we can test with your workbook on our end and see if we can reproduce the issue.

To protect your privacy, I will send you a private message (pm) to collect the sample workbook. Please click here to access pm.

Regards,

Neha

[Edited by Neha Singh MSFT, 07:45 AM(UTC), Jul 22, 2020]

Answer 2

Hi Tami,

I think I made a mistake in my test, please allow me some time, I will check further and will update you with my result.

Thanks for your patience.

Kind Regards,

Neha

Answer 3

Thanks for your check. I guess the issue is not that using AND or OR will error out.

But the results may not be correct as per screenshots from Tami.

I suggest to check same results in Online and local version.

Answer 4

Hi Tami,

Thanks for your patience.

I would like to make a summary of the post, so it’s easier to find the information.

Issue Description:

Using AND and OR function with range does not give the correct result, whereas when an individual cell is used, it shows the correct result.

The issue was first noticed in Version 2003 (Build 12624.20382).

Environment:

Excel 365 for Windows

Test Result:

I tested with the following versions of Office and they showed the same result:

<br>Current Channel	<br>Version 2006 (Build 13001.20384)
<br>Semi-Annual Enterprise Channel	<br>Version 2002 (Build<br> 12527.20880)
<br>Beta Channel	<br>Version 2008 (Build 13117.20000)
<br>Current Channel (Preview)	<br>Version 2007 (Build 13029.20236)

Excel shows the correct result in Version 2002 (Build 12527.20278). 

Suggestion:

On further testing, it appears that if we add @ before the range name in AND and OR function, Excel will start showing the correct result. 

For example *=AND(**@Years_Under_Contract>=8,@Number_of_Titles_in_Print>=8,@*Number_of_Books_Sold>=200000).

I have created a small video for your reference, I’ll upload the video and fixed sample workbook in Private message.

@ sign means “at this row” which means it will not consider the whole column. If we remove @ sign, you will observe that the active area expands to the whole column. Consider the following GIF to understand this phenomenon:

Though I didn’t find the reason behind AND and OR showing the correct result without using @ in an older version of Excel and why this behavior was changed in later versions but at least now with this workaround, we can get the correct data when we use range.

Besides, to report this, I’ll suggest you use feedback took right from the Excel app itself, click File > Feedback. This is the quickest route to get your comments to the developers’ ears, this will have more impact as the feedback is coming directly from the customer.

We appreciate your understanding. Have a nice day and stay safe 😊

With sincerest regards,

Neha