Maxifs one less than max, two less than max, etc

Will85

Board Regular
Joined
Apr 26, 2012
Messages
240
Office Version
  1. 365
Platform
  1. Windows
I am trying to create a tool for users to view the $ value of each of the 10 most recent customer orders.

My accounting data is on sheet2: column A contains the customer number, Column B contains the order number, and column C contains the $ value

On sheet1 I have a drop down box in cell A1, this drop down box looks up a unique list of customer numbers contained on sheet3.

On sheet 1 In cells B1:B10 I want each of the 10 most recent $ values of each order. B1 is the most recent, B2 is the second most recent, B3 third most recent, etc.

I can use the maxif formula to find the maximum order number for a given customer, I know that is my most recent order.

However, I dont know what the second most recent order number is for this particular customer.

Im not sure if this is possible to do without a macro. The drop down will be used rapidly to change between customers. I need the results to update as the customer is changed.

Do I need a macro that is triggered by changing my combobox drop down?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Yup.

Jus change the range from B2:C100 to B2:B100
That works, its giving me the correct Order number, but its giving me a refed out value in the second column. I only need one column
 
Upvote 0
all I need is column J from your example, which its doing, but it also automatically gave me values in the next column. Ive never seen a formula like this.
 
Upvote 0
Oops forgot it had the index, try
Excel Formula:
=INDEX(SORT(FILTER(B2:B100,A2:A100=I2),1,-1),SEQUENCE(10))
 
Upvote 0
Oops forgot it had the index, try
Excel Formula:
=INDEX(SORT(FILTER(B2:B100,A2:A100=I2),1,-1),SEQUENCE(10))
That worked, thank you! Is this a new type of formula for Office 360? Ive never seen something like this.
 
Upvote 0
Sort, Filter & Sequence are dynamic array functions & they were released Nov/Dec 2019, so they are fairly new functions.
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

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