[SOLVED] _xlfn.MAXIFS Error - Trying To Recreate The Formulas

• Register
• Help
• Forgotten Your Password?
• Remember Me?

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 

• Forum
  • Today's Posts
  • FAQ
  • Calendar
  • Community
    • Groups
  • Forum Actions
    • Mark Forums Read
  • Quick Links :
• What's New?
• Members List
• Calendar
• Forum Rules
• Dashboard
• Commercial Services

• Advanced Search

• 
• Forum
• Microsoft Office Application Help - Excel Help forum
• Excel General
• [SOLVED] _xlfn.MAXIFS error - Trying to recreate the formulas

+ Reply to Thread Results 1 to 14 of 14 _xlfn.MAXIFS error - Trying to recreate the formulas

• LinkBack

  • LinkBack URL
  • About LinkBacks

• Thread Tools

  • Show Printable Version
  • Subscribe to this Thread…

• Rate This Thread

  • Current Rating
  • ‎ Excellent
  • ‎ Good
  • ‎ Average
  • ‎ Bad
  • ‎ Terrible

• Display

  • Linear Mode
  • Switch to Hybrid Mode
  • Switch to Threaded Mode

1. 03-27-2018, 03:33 AM #1 Exodus_NZ
   • View Profile
   • View Forum Posts
   Registered User Join Date 08-21-2013 Location New Zealand MS-Off Ver Excel 2016 Posts 81

   _xlfn.MAXIFS error - Trying to recreate the formulas

   Hi guys I am getting _xlfn.MAXIFS - Which ive found to mean that my friend made this spreadsheet in the new office version! So unfortunately since I am on 2016 I am stuck as this feature is not supported on my office! Anyways - I am trying to get some advice on his formula and how I can fix it / make it work on my version of office! Again any advice would be awesome!

   Attached Files
   • NEW-Rank.xlsm‎ (23.0 KB, 38 views) Download
   Register To Reply
2. 03-27-2018, 03:48 AM #2 PaulM100
   • View Profile
   • View Forum Posts
   • Visit Homepage
   Forum Expert Join Date 10-09-2017 Location UK MS-Off Ver Office 365 Posts 2,108

   Re: _xlfn.MAXIFS error - Trying to recreate the formulas

   Hi, You can use a MAX(IF( formula instead of MAXIFS. For example this: =MAXIFS($A$14:$A23,$D$14:$D23,$D24) Can be changed into something like this : =MAX(IF($D$14:$D23=$D24,$A$14:$A23)) Array Entered.

   Register To Reply
3. 03-27-2018, 03:54 AM #3 Exodus_NZ
   • View Profile
   • View Forum Posts
   Registered User Join Date 08-21-2013 Location New Zealand MS-Off Ver Excel 2016 Posts 81

   Re: _xlfn.MAXIFS error - Trying to recreate the formulas

   Hi Thanks for that So for example H24 would become this?? Please Login or Register to view this content.

   Register To Reply
4. 03-27-2018, 04:00 AM #4 tim201110
   • View Profile
   • View Forum Posts
   Forum Expert Join Date 10-23-2011 Location Russia MS-Off Ver 2016, 2019 Posts 2,357

   Re: _xlfn.MAXIFS error - Trying to recreate the formulas

   you can use =AGGREGATE(14,6,$A$14:$A23/($E$14:$E23=$D24),1) it is not array formula

   Register To Reply
5. 03-27-2018, 04:03 AM #5 PaulM100
   • View Profile
   • View Forum Posts
   • Visit Homepage
   Forum Expert Join Date 10-09-2017 Location UK MS-Off Ver Office 365 Posts 2,108

   Re: _xlfn.MAXIFS error - Trying to recreate the formulas

   Also, your VLOOKUPs are broken: VLOOKUP((MAX(MAX(IF($D$14:$D23=$D24,$A$14:$A23)),MAX(IF($E$14:$E23=$D24,$A$14:$A23)))),$A$14:$I23,10) and VLOOKUP((MAX(MAX(IF($D$14:$D23=$D24,$A$14:$A23)),MAX(IF($E$14:$E23=$D24,$A$14:$A23)))),$A$14:$I23,11) But you range is from A to I so you have only 9 column so you will get a #REF! error. Investigate them first

   Register To Reply
6. 03-27-2018, 04:14 AM #6 Exodus_NZ
   • View Profile
   • View Forum Posts
   Registered User Join Date 08-21-2013 Location New Zealand MS-Off Ver Excel 2016 Posts 81

   Re: _xlfn.MAXIFS error - Trying to recreate the formulas

   Thanks again paul - Yep I deleted COL 10 and 11 for my example, they currently are just blank 1s on my master copy ive added them into my example again to try work it out Using either your suggestion or tims

   Last edited by Exodus_NZ; 03-27-2018 at 04:38 AM.

   Register To Reply
7. 03-27-2018, 04:38 AM #7 Exodus_NZ
   • View Profile
   • View Forum Posts
   Registered User Join Date 08-21-2013 Location New Zealand MS-Off Ver Excel 2016 Posts 81

   Re: _xlfn.MAXIFS error - Trying to recreate the formulas

   Hi again, ive managed to get it going using this. =IF((VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,4))=D27,(VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,10)),(VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,11)))+L27 So one COLUMN is working, I will work on another soon, however now it is running very slow - Would Aggregate work faster?

   Register To Reply
8. 03-27-2018, 04:39 AM #8 PaulM100
   • View Profile
   • View Forum Posts
   • Visit Homepage
   Forum Expert Join Date 10-09-2017 Location UK MS-Off Ver Office 365 Posts 2,108

   Re: _xlfn.MAXIFS error - Trying to recreate the formulas

   There is only one way to know. Use it in the formula. Probably it will be faster.

   Register To Reply
9. 03-27-2018, 04:43 AM #9 Exodus_NZ
   • View Profile
   • View Forum Posts
   Registered User Join Date 08-21-2013 Location New Zealand MS-Off Ver Excel 2016 Posts 81

   Re: _xlfn.MAXIFS error - Trying to recreate the formulas

   Good point! Do you still use the VLOOKUP then the AGGREGATE(14,6,$A$14:$A23/($E$14:$E23=$D24),1) ??

   Register To Reply
10. 03-27-2018, 04:44 AM #10 PaulM100
    • View Profile
    • View Forum Posts
    • Visit Homepage
    Forum Expert Join Date 10-09-2017 Location UK MS-Off Ver Office 365 Posts 2,108

    Re: _xlfn.MAXIFS error - Trying to recreate the formulas

    Yes, the aggregate formula posted by tims can replace these 2 formulas: =MAXIFS($A$14:$A23,$D$14:$D23,$D24) =MAX(IF($D$14:$D23=$D24,$A$14:$A23))

    Register To Reply
11. 03-27-2018, 04:59 AM #11 Exodus_NZ
    • View Profile
    • View Forum Posts
    Registered User Join Date 08-21-2013 Location New Zealand MS-Off Ver Excel 2016 Posts 81

    Re: _xlfn.MAXIFS error - Trying to recreate the formulas

    Thanks Paul you've been great So this is where im up to now - Ive changed it and I get a #NUM! error Thought 14,6 was meant to ignore that lol =IF((VLOOKUP((MAX(AGGREGATE(14,6,$A$17:$A26/($D$17:$D26=$D27),1),AGGREGATE(14,6,$A$17:$A26/($E$17:$E26=$D27),1))),$A$17:$K26,4))=D27,(VLOOKUP((MAX(AGGREGATE(14,6,$A$17:$A26/($D$17:$D26=$D27),1),AGGREGATE(14,6,$A$17:$A26/($E$17:$E26=$D27),1))),$A$17:$K26,10)),(VLOOKUP((MAX(AGGREGATE(14,6,$A$17:$A26/($D$17:$D26=$D27),1),AGGREGATE(14,6,$A$17:$A26/($E$17:$E26=$D27),1))),$A$17:$K26,11)))+L27 =IF((VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,4))=D27,(VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,10)),(VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,11)))+L27

    Register To Reply
12. 03-27-2018, 05:55 AM #12 PaulM100
    • View Profile
    • View Forum Posts
    • Visit Homepage
    Forum Expert Join Date 10-09-2017 Location UK MS-Off Ver Office 365 Posts 2,108

    Re: _xlfn.MAXIFS error - Trying to recreate the formulas

    I've updated your formulas in row 27, marked in blue, but I am not sure if I understood your logic there. See attached.

    Attached Files
    • NEW-Rank (4).xlsm‎ (21.3 KB, 28 views) Download
    Register To Reply
13. 03-28-2018, 03:04 AM #13 Exodus_NZ
    • View Profile
    • View Forum Posts
    Registered User Join Date 08-21-2013 Location New Zealand MS-Off Ver Excel 2016 Posts 81

    Re: _xlfn.MAXIFS error - Trying to recreate the formulas

    Thanks PaulM100! I will stick with the MAX(IF... As I could not get the Aggregate working (even tho it could be faster!) Thanks again for your help - Everything seems to be working now that I have copied it onto the master - And thanks for doing the other cells I copied and pasted those as well!

    Register To Reply
14. 03-28-2018, 03:05 AM #14 PaulM100
    • View Profile
    • View Forum Posts
    • Visit Homepage
    Forum Expert Join Date 10-09-2017 Location UK MS-Off Ver Office 365 Posts 2,108

    Re: _xlfn.MAXIFS error - Trying to recreate the formulas

    no worries, glad to help. Please mark the thread as Solved if that took care of your problem. Thanks.

    Register To Reply

+ Reply to Thread « Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

1. _xlfn.IFERROR Named Range?

   By ptmuldoon in forum Excel General Replies: 9 Last Post: 05-09-2019, 12:38 PM

2. Recreate raw data into a legible table using formulas

   By john dalton in forum Excel Formulas & Functions Replies: 0 Last Post: 02-28-2018, 07:50 AM

3. [SOLVED] MaxIFs help

   By Hondahawkrider in forum Excel Formulas & Functions Replies: 5 Last Post: 03-25-2016, 02:23 PM

4. MaxIFS help

   By Hondahawkrider in forum Excel Formulas & Functions Replies: 1 Last Post: 01-06-2016, 03:17 PM

5. [SOLVED] windows.activate Error I can't recreate!

   By virgincinboy in forum Excel Programming / VBA / Macros Replies: 2 Last Post: 04-29-2013, 09:39 AM

6. _xlfn.AVERAGEIFS convert to 2003

   By ChrisRoc22 in forum Excel General Replies: 1 Last Post: 10-05-2012, 07:30 AM

7. _xlfn.AVERAGEIF ... How do I remove this?

   By John Bates in forum Excel General Replies: 2 Last Post: 08-21-2008, 10:42 AM

Bookmarks

Bookmarks

• Digg
• del.icio.us
• StumbleUpon
• Google

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

• BB code is On
• Smilies are On
• [IMG] code is Off
• HTML code is Off
• Trackbacks are Off
• Pingbacks are Off
• Refbacks are Off

Forum Rules

-- vB4 Default Style -- Mobile Style Premium -- Lightweight -- Fully Optimized -- Mobile_V1

• Contact Us
• ExcelForum.com
• Archive
• Top

All times are GMT -4. The time now is 09:08 PM. Search Engine Friendly URLs by vBSEO 3.6.0 RC 1