Help IF function bug

Thomasla

New Member
Joined
Mar 20, 2019
Messages
2
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



NameNumberTopNameResult
Paul11Paul=LARGE(IF(A2:A6=D2;B2:B6);C2)
Chef2
Chef3
Chef4
Paul6

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,212
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

New Member
Joined
Mar 20, 2019
Messages
2
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

Well-known Member
Joined
Oct 5, 2015
Messages
1,023
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,212
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,966
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top