Need a alternate solution for Maxifs

Divakarkumar

New Member
Joined
Jul 31, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Maxif.png


Hi Team,

I have used Maxifs (=MAXIFS($B$2:$B$13,$A$2:$A$13,A2)=B2) to find latest version of quote number(False/ True) but my bad luck the same Maxifs forumal is not available in my Virtual Machine due to excel version issue.

Examples : I have 10 number repeated in A columns 5 times and B column Version like 12345 in the same row and I have to get 4 false and 1 true which is latest one (10 and 5) , I have attached image for better understate.

False : indicate old version
True : Indicate new version

Great if some one provide alternate option to built same logic.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,564
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You did not post an example that we can use; see XL2BB.
Adapt the following to your data.

T202002a.xlsm
BCDE
1Max based on criteria
2A200FALSEA
3B220FALSE1,320
4C240FALSE1,320
5A260FALSE
6B280FALSE
7C300FALSE
8A1320TRUE
9B340TRUE
10C360TRUE
Installations
Cell Formulas
RangeFormula
E3E3=MAX((Installations!B2:B455=E2)*Installations!C2:C455)
E4E4=AGGREGATE(14,6,Installations!C$2:C$455/(Installations!B$2:B$455=E2),1)
D2:D10D2=AGGREGATE(14,6,Installations!C$2:C$455/(Installations!B$2:B$455=B2),1)=C2
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Divakarkumar

New Member
Joined
Jul 31, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
You did not post an example that we can use; see XL2BB.
Adapt the following to your data.
========================

Hi Sir,

Your result is coming as i expected but i'm not able to understand the formula which you have used, could you please use the same formula in below data set in C column, need false or true result based on latest quote.

and I really appreciate your help


Quote NumbersPVR VersionLatest PVR Version
101
112
103
114
145
156
147
158
189
105
1811
1912
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Welcome to the MrExcel board!

Adapting the last formula suggested in post 3 to your data layout does exactly as the MAXIFS formula.

20 07 31.xlsm
ABCD
1Quote NumbersPVR VersionLatest PVR Version
2101FALSEFALSE
3112FALSEFALSE
4103FALSEFALSE
5114TRUETRUE
6145FALSEFALSE
7156FALSEFALSE
8147TRUETRUE
9158TRUETRUE
10189FALSEFALSE
11105TRUETRUE
121811TRUETRUE
131912TRUETRUE
MAXIFS Replacement
Cell Formulas
RangeFormula
C2:C13C2=MAXIFS($B$2:$B$13,$A$2:$A$13,A2)=B2
D2:D13D2=AGGREGATE(14,6,B$2:B$13/(A$2:A$13=A2),1)=B2
 

Divakarkumar

New Member
Joined
Jul 31, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Thanks you so much for your help Sir, really I appreciate your help
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Dave did the work though - I just tweaked the ranges. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,114
Members
414,125
Latest member
iQQ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top