Maximum currency

[EinarOSies]

Please I need help in finding the maximum value in a range.

In column J are a combination of strings and formulas. I attached an info column K to aid in understanding my problem and formula in column J.
How can I input a formula to get the largest or maximum value in column J?

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Try something like

=AGGREGATE(14,6,MID(J4:J100,5,20)+0,1)

 

[EinarOSies]

Please thanks it worked. But what about the minimum and average value

 

[Peter_SSs]

Minimum

=AGGREGATE(15,6,MID(J4:J100,5,20)+0,1)

Average (assuming no blanks in the range)

=AVERAGE(MID(J4:J15,5,20)+0)

In your Excel version you may need to confirm this one with Ctrl+Shift+Enter, not just Enter

 

[EinarOSies]

Please another follow-up question looking at the data I would want to calculate the cells without the the "-" or with negative values thanks

 

[Peter_SSs]

So you want max, Min & Average for the positive values only?
.. or for non-negative values (that is include any zero values in the calculations)?

 

[EinarOSies]

Please only positive values

 

[Peter_SSs]

Please only positive values

Try something like these:

=AGGREGATE(14,6,MID(J4:J115,5,20)/(MID(J4:J15,5,20)+0>0),1)

=AGGREGATE(15,6,MID(J4:J15,5,20)/(MID(J4:J15,5,20)+0>0),1)

=AVERAGE(IF(MID(J4:J15,5,20)+0>0,(MID(J4:J15,5,20)+0),""))

 

[EinarOSies]

Thanks for the constant help. With the average function I wanted to add a range of values to find its aveverage that is from 0-to-40, so I tried adding an AND argument to the function to be

=AVERAGE(IF(AND(MID(J4:J107,5,20)+0>0,MID(J4:J107,5,20)+0<40),(MID(J4:J107,5,20)+0),""))

But it gave me a #VALUE error any suggestion

 

[EinarOSies]

Thanks for the constant help. With the average function I wanted to add a range of values to find its aveverage that is from 0-to-40, so I tried adding an AND argument to the function to be

=AVERAGE(IF(AND(MID(J4:J107,5,20)+0>0,MID(J4:J107,5,20)+0<40),(MID(J4:J107,5,20)+0),""))

But it gave me a #VALUE error any suggestion

Please I will appreciate it if the range from 1-to-40 will be applied to the Minimum and Maximum values.
Thanks