# Maximum currency

#### EinarOSies

##### Board Regular
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?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Peter_SSs

##### MrExcel MVP, Moderator
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

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

#### EinarOSies

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

#### Peter_SSs

##### MrExcel MVP, Moderator
Minimum
Excel Formula:
``=AGGREGATE(15,6,MID(J4:J100,5,20)+0,1)``

Average (assuming no blanks in the range)
Excel Formula:
``=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

##### Board Regular

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

##### MrExcel MVP, Moderator
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)?

#### Peter_SSs

##### MrExcel MVP, Moderator
Try something like these:
Excel Formula:
``=AGGREGATE(14,6,MID(J4:J115,5,20)/(MID(J4:J15,5,20)+0>0),1)``
Excel Formula:
``=AGGREGATE(15,6,MID(J4:J15,5,20)/(MID(J4:J15,5,20)+0>0),1)``
Excel Formula:
``=AVERAGE(IF(MID(J4:J15,5,20)+0>0,(MID(J4:J15,5,20)+0),""))``

#### EinarOSies

##### Board Regular
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
Excel Formula:
``=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

##### Board Regular
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
Excel Formula:
``=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

Replies
1
Views
122
Replies
11
Views
191
Replies
3
Views
92
Replies
3
Views
112
Replies
4
Views
383

1,141,308
Messages
5,705,666
Members
421,403
Latest member
cmowla

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back