Learn Excel 2010 - "MAXIF": Podcast #1518

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Feb 21, 2012.
With a list of Products and a list of Dates, Bill's mission is to find the last order date for each product in the Dataset. Learn the use of MAXIF, changing Number Formats, and more in Episode #1518.
[Previously slated as Episode #1516]
maxresdefault.jpg


Transcript of the video:
MrExcel podcast is sponsored by the Excel pro weekly newsletter published by CFO and written by me.
Learn Excel form MrExcel podcast episode 1516, MAXIF?
Hey, welcome back to the mystery Excel netcast.
I'm Bill Jelen.
Here is today's question.
Today's question is we have a bunch of products listed here in column A and the order date I want to find the last order date for each product and I can't guarantee that the products will be sorted higher, you know the latest earliest, earliest to latest.
If it was latest earliest, more than I'm easy it'd just be a simple VLOOKUP, but can't guarantee that.
Now, the old DMAX function would do this, but I'm gonna try and do it a different way here.
I'm gonna ask for equal IF.
equal IF go look through all of these product names over here in column A.
I'll press F4, if that is equal to this product, then I want the date, the corresponding date from column B.
Press F4 there otherwise I want a zero.
Now, this is gonna work because I'm looking for the max.
If i was looking for the min and instead of a zero, there I wanna put a really large number.
All right! Now, this is going to return an array of answers in this case I have 18 rows you can't see down to row 19, but I have 18 rows of data.
So, it's going to return 18 answers and what I need to do is get the max from that.
So, the max becomes the wrapper function, WRAPPER, wrapper function and of course we have to do hold down ctrl + shift and press enter In order to get that answer, now, hey we recognize this anytime you get an answer in the 40,000 range 37,043 somewhere in that range and you're expecting a date, it's the right answer in the wrong format.
So, we go through and change that copy it down and now for each product we will see the latest order.
Now, let's just test this.
I'm gonna come here to product B I'm gonna put in a order date far in the future like 1/1/2013 and sure enough it is pulling it there, I could we do this with DMAX we could, but we'd have to have a separate criteria range for each of the products.
So, this is a little bit better while it's more confusing a lot of people aren't familiar the array formulas, it gets it all done in a single formula.
Oh! Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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