linking cells to "max" function 1

[jbknudsen]

Greetings to all. I am tracking shipments each week for a year in excell. I need to know the max shipment and the date that it was received. For example I will note the weight of the shipment of resin and the date the shipment arrived.

I have 52 fields for the weight and the next row has the date. I have used the "max" function to return the max weight. I have tried the "(If=xxx,xxx" but that works only for the first eight entries.

The Question.
How can I have a cell return the highest weight on record for the year and return the associated date?

Thanks in advance for any help.

Jay

 

[SacreBleu]

Use MATCH and the OFFSET functions.

 

[jbknudsen]

I'll give it a shot,

Thanks,

Jay

 

[pmover]

another technique, use the standard filter features within excel. from menu, select data=>filter=>autofilter.

or

 

[bltseattle]

Say you have your weights in b1:zz1 and dates in b2:zz2

=MATCH(MAX(b1:zz1),b1:zz1,0)

will return the position in the list of the max value, ie the "offset" from the starting column. To return the date, you combine it with OFFSET as follows:

date=OFFSET(starting_cell,row_offset,col_offset,#rows_in_selection,#cols_in_selection)



date=OFFSET(b2,0,MATCH(MAX(b1:zz1),b1:zz1,0)-1,1,1)

or alternatively

date=OFFSET(b1,0,MATCH(MAX(b1:zz1),b1:zz1,0),1,1)


Haven't tried it yet but this should get you close

 

[SacreBleu]

Not really related to the original post, but various combinations of INDEX, MATCH, and OFFSET provide one with a much more useful "toolbox" than simply using the various LOOKUP functions.

My $0.02

 

[jbknudsen]

Howdy to all,
Thanks for the input. bltseattle,I have been somewhat successful with your suggestion. The function "max" is working and the function "offset" seems to be working. However, the offset returns the date that is in the cell below and to the right of the cell with the maximum value. I did test different cells with maximum values and the offset was consistent, one cell below and one cell to the right.

We are getting close
Below is a copy/past of the cell that is returning the date
=OFFSET(B2,0,MATCH(MAX(B1:N1),B1:N1,0),1,1)

Thanks,

Jay

 

[SacreBleu]

When doing the combined functions, I sometimes start out as putting each function in its own cell, to troubleshoot it. Once it works OK, then I combine the various functions in one cell. In some cases, sometimes it isn't even worthwhile to combine functions, and only increase file size slightly.

 

[bltseattle]

OK, if your values are in B1:N1 and the dates are in B2:N2 then try:

=OFFSET(a2,0,MATCH(MAX(B1:N1),B1:N1,0),1,1)

This assumes the dates are in B2:N2.


If this does not work correctly please let me know the ranges where the dates and values, respectively, are located and we can nail this thing down!

 

[jbknudsen]

Well, good morning all,

bltseattle,
Smokin deal!!!! Your last suggestion works like a champ.

Thanks for the help and everyones effort with this.

Cheers,

Jay