Help with MAXIFS and MINIFS

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
The following formula seems to work. =MINIFS(F4:X4,$G$7:$Y$7,"COMPLIANT"). In the range F4:X4, I want to find the MIN of F4,H4,J4,L4,N4,P4,R4,T4,V4,X4 if the condition applies. (I'll want to do the MAXIFS as well)
I use the following VBA to add rows to the table
VBA Code:
Do Until z = 0
Set tblRow = tbl.ListRows.Add
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
z = z - 1
Loop
The code works well except that the MINIFS formulas get changed to F4:X9, F5:X10, F6:X11, etc. instead of F4:X4, F5:X5, F6:X6, etc.

Need help to accomplish this, not necessarily with using MINIFS or MAXIFS. Thanks
 

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.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
What happens if you remove the copy and paste lines?

As you're working with a table formulas and formats should be automatic consistent with the rest of the column. Attempting to copy and paste could be breaking the relative pattern of the formula.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
Gonna need more info then, the cause of the problem is something other than the code.
 

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This seems to work for finding MAX (Cell refs and formats copy down properly)
{=MAX(IF(CHOOSE({1,2,3,4,5,6,7,8,9,10},$G$7,$I$7,$K$7,$M$7,$O$7,$Q$7,$S$7,$U$7,$W$7,$Y$7)="COMPLIANT",CHOOSE({1,2,3,4,5,6,7,8,9,10},F4,H4,J4,L4,N4,P4,R4,T4,V4,X4))))}
It does not work for finding MIN because it considers blank cells as 0 (zero). I need a way to exclude blank cells because data could = 0.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
If that works then your original formula should also work, with 2 exceptions.
1- Opening the workbook in a version of excel that doesn't support MINIFS or MAXIFS
2- The criteria of "COMPLIANT" is not found in any of the specified cells, or if it is found in any of the other cells in $G$7:$Y$7 that you have not included in your new formula.

But neither of these exceptions would affect how the ranges are copied down from row to row.

Although, differentiating between empty and 0 could be difficult with MAXIFS / MINIFS.

Try this for MIN, change 15 to 14 for MAX

=AGGREGATE(15,6,F4:X4/(F4:X4<>"")/($G$7:$Y$7="COMPLIANT"),1)
 

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I was working with the AGGREGATE formula. What you suggested still copies to F4:X9, F5:X10, F6:X11. Made the correction, put data to evaluate and get a #NUM! error. This formula =AGGREGATE(5,6,(F4,H4,J4,L4,N4,P4,R4,T4,V4,X4)) works perfectly to find the MIN and AGGREGATE(15,6,(F4,H4,J4,L4,N4,P4,R4,T4,V4,X4),1) works to find SMALL. Tried using Option 7 because I have hidden columns however it changes nothing.
 

Attachments

  • Annotation 2020-05-02 143700.jpg
    Annotation 2020-05-02 143700.jpg
    114.1 KB · Views: 4

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,471
Office Version
  1. 365
Platform
  1. Windows
It only ignores hidden rows, not hidden columns, although I don't see evidence of either being hidden in your screen cap.

Also, as I already said, the type of formula that you use will NOT affect the way that the rows are being incorrectly entered, that is down to something else which has not yet been identified.

I would use this method to insert the formula instead of trying to copy and paste, change the range as needed.
VBA Code:
Range("D4:D8").FormulaR1C1 = "=AGGREGATE(15,6,RC6:RC24/(RC6:RC24<>"")/(R7C7:R7C25="COMPLIANT"),1)"
Range("E4:E8").FormulaR1C1 = "=AGGREGATE(14,6,RC6:RC24/(RC6:RC24<>"")/(R7C7:R7C25="COMPLIANT"),1)"
 

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
I am posting the worksheets so you can better understand what I am trying to accomplish. This first sheet is the Prep sheet. There is a macro located in F1(SetupBidders) to create the BIDDERS table and another macro located in F3 for items to evaluate (Setup Items). Those two tables change from one project to the next. The Reset Calc button (In the Red area), unhides all the columns and deletes the rows except one in the Calc sheet. Same with Reset Prep (Also in the Red area). Clears the data and resets the two tables. Below is the prep sheet. Looks like you can't see the buttons

SOA Selection tool current formulas test.xlsm
ABCDEFGHIJKL
1RESULTS FOR PROJECT NUMBER4NUMBER OF BIDDERS (Max 10)
23
35NUMBER OF ITEMS TO BE EVALUATED
44
5
6
7BIDDERSITEMS TO EVALUATE
8No.NAMENo.Description Weight Factor Type
91Bidder11Journeyman4Dollars
102Bidder22Apprentice3Dollars
113Bidder33Labourer1Dollars
124Bidder44Unit Travel Rate4Dollars
135Material Mark-up2Percent
14
15
16
Prep
 

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
Based on the example above, this is what the Calc sheet looks like. You latest proposed AGGREGATE formula did not work. For some reason Excel does not seem to like R7C7:R7C25,it wants to change it to RC77:RC725

SOA Selection tool current formulas test.xlsm
ABCDEFGHIJKLMZ
1RESULTS FOR PROJECT NUMBER0        
2LOW BIDS HIGHLIGHTED IN YELLOWBidder1Bidder2Bidder3Bidder4
3No.DescriptionWeight FactorsLowest rateHighest rateBidScoreBid2Score2Bid3Score3Bid4Score4
41Journeyman4#NUM!#NUM!    
52Apprentice3#NUM!#NUM!    
63Labourer1#NUM!#NUM!    
74Unit Travel Rate4#NUM!#NUM!    
85Material Mark-up2#NUM!#NUM!    
9 0.000.000.000.00
10ERRORERRORERRORERROR
11COMPLIANCECOMPLIANTCOMPLIANTCOMPLIANTCOMPLIANT
12RANK1111
13
Calc
Cell Formulas
RangeFormula
A1A1=Prep!A1
D1D1=Prep!C1
F1,H1,J1,L1F1=IF(F2=0,0,"")
G1,I1,K1,M1G1=IF(F2=0,0,"")
A4:A8A4=ROW()-3
B4:B8B4=Prep!E9
D4:D8D4=AGGREGATE(15,6,F4:X8/(F4:X8<>"")/($G$11:$Y$11="COMPLIANT"),1)
E4:E8E4=AGGREGATE(14,6,F4:X8/(F4:X8<>"")/($G$11:$Y$11="COMPLIANT"),1)
C4:C8C4=IF(Prep!$F9="","",Prep!$F9)
C9C9=IF(C3="Pondération",SUM(C4:C8),"")
G4:G8,M4:M8,K4:K8,I4:I8G4=IF(F4="","",IF(G$11="NON-COMPLIANT","",IF(F4=$D4,$C4,(1-(F4-$D4)/($E4-$D4))*$C4)))
G9,M9,K9,I9G9=IF(F2=0,"",SUM(G4:G8))
G10,M10,K10,I10G10=IF(G9="","",IFERROR(G9/MAX($G$9:$Y$9),"ERROR"))
G12,M12,K12,I12G12=IF(G9="","",IF(G11="COMPLIANT",COUNTIFS($G$11:$Y$11,"COMPLIANT",$G$9:$Y$9,">"&G9)+1,""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:F8,H4:H8,J4:J8,L4:L8,N4:N8,P4:P8,R4:R8,T4:T8,V4:V8,X4:X8Expression=AND($C4<>"",F4=$D4)textNO
M11Cell Value="NON-COMPLIANT"textNO
M11Cell Value="COMPLIANT"textNO
K11Cell Value="NON-COMPLIANT"textNO
K11Cell Value="COMPLIANT"textNO
I11Cell Value="NON-COMPLIANT"textNO
I11Cell Value="COMPLIANT"textNO
G11Cell Value="NON-COMPLIANT"textNO
G11Cell Value="COMPLIANT"textNO
D4:E8Expression=Prep!$G9="Dollars"textNO
D4:E8Expression=Prep!$G9="Percent"textNO
Cells with Data Validation
CellAllowCriteria
G11,I11,K11,M11,O11,Q11,S11,U11,W11,Y11List ,COMPLIANT,NON-COMPLIANT
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

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