Choose max value two different columns.

[white95gt]

Hello,

i have a series of calculations in two columns running side by side. Sometimes i do not require both columns, sometimes it is required.

towards the end of the calculations i need to merge the two columns and choose the cell with the largest value to continue the final calculations. Also if i do not use the second column, I end up with #DIV/0! as a result and cannot figure out the formula to choose the higher cell value between them.

I tried important a clip of excell, so i made the simple table below to emulate an excell sheet. lets pretend answer 1 is cell:A1.

answer 1		answer 2
150		#DIV/0!
(max value)	the max value between the two columns

<TBODY>
</TBODY>

 

[vds1]

suppose you want to find largest value in column A and C

=MAX(A:A,C:C)

 

[white95gt]

Equation would work if both columns had actual #'s, however sometimes i have the error message and the calculation you provided will not work.

 

[vds1]

You have to provide the range.

=Max(A2:A54,C2:C54)

 

[white95gt]

It still will not operate with the #DIV/0! answer in one column, ive even tried iferror but i can not figure this one out.

 

[vds1]

One more option would be to use AGGREGATE

=AGGREGATE(4,6,A2:A54,C2:C54)

 

[white95gt]

VDS1 thank you very much, that works perfectly.

 

[vds1]

Thanks for your response. In the above formula 4 is for MAX function and 6 is for ignore error values.

 

[white95gt]

As i went through the formula, step by step and broke it down, i was able to understand the 4 and the 6, works perfectly.

I knew there was an answer, thanks for your help.