Formula to determine max number in multiple dynamic arrays

jberylec

New Member
Joined
Jan 14, 2023
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
This one is proving tough for me! Hope the experts can help :)

I need to create a formula in F2 that can be copied down through hundreds of location/batch rows, on a new version of this spreadsheet each day. The formula needs to find the max number in D but only for each location/batch section and report only that one into F on the same row. I've manually typed them in F just for display purposes.

A and B rows are always merged for that entire location and batch, as shown. So I was trying to build a formula that would recognize each time a row in B isn't blank <>"" which indicates a new location/batch starts here, then include that row and all blank rows below it (they're merged so treated as blank cells) to find the MAX number in D and show that in F. Then of course it starts over IF B isnt blank again.

In case of a tie, show both.

FYI, there are a lot more columns of data, not relevant for this though because none can be used as an identifier, I also have many other formula columns that I paste into row 2 each morning, then drag them all down the entire sheet, so trying to keep this just a formula not VBA. Hoping to add this new column to my daily paste and drag routine. Ah. Exhausting just describing this. Ha! Thanks in advance
 

Attachments

  • excel.png
    excel.png
    20.7 KB · Views: 20

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Peter, another quick one for you I'm sure, but I just cant figure this out! I need a formula that grabs the max number from column C and the second highest from column C (that are the same location using our helper column we already have in place) and subtracts them, reporting back just how much higher the max is. So in the quoted example data this new formula would report back a 2 on row 2 because its 2 higher than the next highest, it would report back 4 on row 9, etc etc. All others would be blanks because they're not the max in that loc...

Thanks in advance!

EDIT: the quote didn't work. I'm referring to the example data in post # 26
 
Upvote 0
Is this what you mean?

jberylec.xlsm
ABCF
1LOCVAL
211832
3181 
4177 
5175.5 
610 
710 
8169 
922704
1020 
11266 
12245 
13335441
1430 
1530 
16318 
17395 
18342 
Sheet1
Cell Formulas
RangeFormula
B2:B18B2=B1+(A2<>"")
F2:F18F2=IF(A2="","",AGGREGATE(14,6,C$2:C$18/(B$2:B$18=B2),1)-AGGREGATE(14,6,C$2:C$18/(B$2:B$18=B2),2))
 
Upvote 0
Yes that seems to do it. I ended up coming up with SUMIFS / COUNTIFS - SUMIFS =
Took me a while to figure it out when there's a tie for 2nd highest! Hence the divide by countifs. Anyhow, my version seems to work, but is your aggregate less resource intensive? Thanks again sir!
 
Upvote 0
Ended up going with your aggregate. Quick question back on our good ole IFNA MATCH formula, is there a way to have the {1,2,3,4} part of the below example auto-adjust based on the contents of another cell? Instead of being hard coded as outputting only 1 through 4. Sometimes I need to see 1 through 9 for example. I already tried replacing {1,2,3,4} with {G2} nope. {"G2"} nope again. Ugh...

IFNA(MATCH(COUNTIFS(Q$2:Q$1420,">"&Q2,BK$2:BK$1420,BK2)+1,{1,2,3,4},0),"")

Thanks again!
 
Upvote 0
back on our good ole IFNA MATCH formula, is there a way to have the {1,2,3,4} part of the below example auto-adjust based on the contents of another cell? Instead of being hard coded as outputting only 1 through 4. Sometimes I need to see 1 through 9 for example. I already tried replacing {1,2,3,4} with {G2} nope. {"G2"} nope again.
Suppose the number is in Q2, then try
Excel Formula:
=IF(D2="","",IFNA(MATCH(COUNTIFS(E$2:E$1000,E2,D$2:D$1000,">"&D2)+1,ROW(INDEX(A:A,1):INDEX(A:A,Q$2)),0),""))
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top