Sum funtion

Hi,

Hoping someone can kindly help. Columns V and Y in the attached file show results of cells based on a VLOOK function. Please can someone advise how I may use the results from columns V and Y to find the maximum or minimum values between a range. For example, for row 2 (row numbers not shown but row 1 is the first one in the attachment) this would be:

=MAX(C2,C157)

I don't want to change the VLOOKUP function - I want to put an equation (similar to the one above) in a new cell.

Grateful for any solution as to how I can do this.

Thanks,

Nick

Good afternoon Nick,

I'm not 100% sure I understand what it is you're looking to return from a Max function, as it looks like Row 2 in your picture has multiple inconsistent values.

Hopefully a general description will set you on the right track.
The Max function usually uses a range (such as :=MAX(V2:Y2) rather than a set of points (such as =MAX(V2,Y2). The difference is a range, which uses a semi colon ( : ) takes all cells between the two numbers into account, whereas a set of points only takes into consideration the points listed (in the above case, V2 and Y2).

If that doesn't help at all, let me know, as I believe this is an easily solvable problem.

Originally Posted by Sky188

Good afternoon Nick,

I'm not 100% sure I understand what it is you're looking to return from a Max function, as it looks like Row 2 in your picture has multiple inconsistent values.

Hopefully a general description will set you on the right track.
The Max function usually uses a range (such as :=MAX(V2:Y2) rather than a set of points (such as =MAX(V2,Y2). The difference is a range, which uses a semi colon ( : ) takes all cells between the two numbers into account, whereas a set of points only takes into consideration the points listed (in the above case, V2 and Y2).

If that doesn't help at all, let me know, as I believe this is an easily solvable problem.

Thank you very much for your prompt reply. In the example above, I need it to be:

=MAX(C2,C157)

For example, I want to pick the cell values up from the V and Y rows. Hopefully, this makes sense when looking at the attachment.

Sorry I am not good at all at explaining online.

Thanks,

Nick

I think I understand what you're trying to say. The simplest way to do that is using the if function. Try:

=if(max(range1)>max(range2),max(range1),max(range2))

Originally Posted by Søren Larsen

Something like this:

=MAX(INDIRECT(V2),INDIRECT(Y2)) ?

Thanks for the reply. It is something like this I need but your example did not

It would be the range from cell c2 to c157. Hope this helps

Originally Posted by Søren Larsen

Then this should work:

=MAX(INDIRECT(V2):INDIRECT(Y2))

Assuming that the given range is on the same worksheet. Is this the case?

Thank you so much. Unfortunately no - the cells are in sheet 1 where as this formula will be in sheet 2. Is this possible?

Originally Posted by Søren Larsen

Yes, just use this instead:

=MAX(INDIRECT("'Sheet 1'!"&V2):INDIRECT("'Sheet 1'!"&Y2))

Thank you so much for your kind help and to all those that also replied.

Best wishes,

Nick