linking cells to "max" function
linking cells to "max" function
(OP)
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
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





RE: linking cells to "max" function
RE: linking cells to "max" function
Thanks,
Jay
RE: linking cells to "max" function
or
RE: linking cells to "max" function
=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
RE: linking cells to "max" function
My $0.02
RE: linking cells to "max" function
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
RE: linking cells to "max" function
RE: linking cells to "max" function
=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!
RE: linking cells to "max" function
bltseattle,
Smokin deal!!!! Your last suggestion works like a champ.
Thanks for the help and everyones effort with this.
Cheers,
Jay