×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

linking cells to "max" function

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

RE: linking cells to "max" function

Use MATCH and the OFFSET functions.

RE: linking cells to "max" function

(OP)
I'll give it a shot,

Thanks,

Jay

RE: linking cells to "max" function

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

or

RE: linking cells to "max" function

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

RE: linking cells to "max" function

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

RE: linking cells to "max" function

(OP)
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

RE: linking cells to "max" function

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.

RE: linking cells to "max" function

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!

RE: linking cells to "max" function

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close