Maximum currency

EinarOSies

Board Regular
Joined
Feb 15, 2021
Messages
51
Office Version
  1. 2019
Platform
  1. Windows
Please I need help in finding the maximum value in a range.
Capture.JPG

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
Joined
May 28, 2005
Messages
50,606
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2021
Messages
51
Office Version
  1. 2019
Platform
  1. Windows
Please thanks it worked. But what about the minimum and average value
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,606
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2021
Messages
51
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
50,606
Office Version
  1. 365
Platform
  1. Windows
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)?
 
Solution

EinarOSies

Board Regular
Joined
Feb 15, 2021
Messages
51
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Please only positive values
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,606
Office Version
  1. 365
Platform
  1. Windows
Please only positive values
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
Joined
Feb 15, 2021
Messages
51
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Feb 15, 2021
Messages
51
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top