Help IF function bug

[Thomasla]

Hi guys,


First of all, thank you for all the content you can find on this site and for the help you provide.

I publish today because recently I have a unexplainable problem on excel:

For 1 month, I have been working on a file where I mainly use IF / Large Functions. My formulas worked very well, but since 1 week it doesn't work anymore. Do you know if there was an update on the function or had you already encountered the problem?

Here is an example of a formula that worked 2 weeks ago and now nothing more. The objective of the formula is to show the maximum number that the first name Paul can have in the table. The “if“ function no longer works as before, i dont understand. The result has to be 6 but it shows 0….

Thank you in advance, and I remain at your disposal if you need more information

Cheers


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Number[/TD]
[TD]Top[/TD]
[TD]Name[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Paul[/TD]
[TD]=LARGE(IF(A2:A6=D2;B2:B6);C2)[/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chef[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the Board. Indeed, there is a lot of good information and helpful people here.

My first thought is that your formula is an array formula. This requires you to press Control+Shift+Enter when you enter the formula. Did you somehow update the formula and not press CSE? Try selecting the cell with the formula, press F2 to edit it, then hold down the Control and Shift keys, and press Enter. Let us know if that works.

 

[Thomasla]

Welcome to the Board. Indeed, there is a lot of good information and helpful people here.

My first thought is that your formula is an array formula. This requires you to press Control+Shift+Enter when you enter the formula. Did you somehow update the formula and not press CSE? Try selecting the cell with the formula, press F2 to edit it, then hold down the Control and Shift keys, and press Enter. Let us know if that works.

Thanks a ton, quick and effective

 

[jmacleary]

I agree with Eric W - youi have probably turned it into a normal formula. This alternative formula (also an array formula) would do the same for you: =MAX(IF(A2:A6=D2,B2:B6),C2)
Enter it in the cell and then press ctrl-shift-enter.

 

[Eric W]

If you have Office 365, there is a non-array function you can use:

=MAXIFS(B:B;A:A;D2)

There's a MINIFS too. But there isn't a LARGEIFS function, you if you want the second largest, you'd have to go back to the array formula. In any case, glad we could help.