MAX formula with negative values

[Rookie2016]

All,

I'm using the formula below but having issues when the value it is selecting is negative. Because column 7 can have a zero, it will choose that rather than the negative value in column 8, which is what I need.

Can someone assist?


=(MAX(INDEX('Sheet2!'$1:$1048576,MATCH(A1,'Sheet2!'J:J,0),N(CHOOSE({1,2},7,8)))))

[protonLeah]

Please Login or Register  to view this content.

ARRAY ENTER...

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[Rookie2016]

protonLeah - thank you but since I sort this formula, I cannot use an array.

kvsrinivasamurthy - This worked perfectly; however, if both columns have a value of zero I get a #NUM! error message. How can I change this so a zero is the result?

[shukla.ankur281190]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[kvsrinivasamurthy]

Pl attach a sample file, not image, showing the problem.

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[Rookie2016]

kvsrinivasamurthy - thank you so much. That worked perfectly!