turning negative values in a sheet to 0 2

[dreadnaught]

I have a sheet that is 180 by 42,000 lines and I need to remove all the negative numbers in order to get the positive order min for a column. Is there a way to remove the negatives to use the normal formula or is there a way to use min() without it looking at a negative number?

 

[JGillespie]

Try using this array formula:

{=MIN(IF(A1:X42000>0,A1:X42000,"&quot)}

replace the ranges with ones that match your data.

You get the curly brackets by usinf CTRL,SHIFT and ENTER together to enter the formula, DO NOT type them in as this will not work.

HTH Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[DaleWatson123321]

Dreadnaught,

Interesting situation. Let me see if I understand...

You have columns with BOTH positive AND negative numbers,
and you need to be able to identify the LOWEST of the POSITIVE numbers ? And I expect that you preferably do NOT want to modify the existing data ? And you want to be able to do this for EACH of the 180 columns ?

My recommendation would be to utilize the "database" capability of Excel, wherein you would have VBA code (attached to a button) which would extract all the POSITIVE numbers from one column (one column at a time), and these numbers would be extracted to a SEPARATE sheet, where you would have a "resident" normal formula (using the MIN function).

This formula would be positioned at the TOP of the separate "extraction" sheet, and would automatically provide the LOWEST POSITIVE number from those (positive) numbers extracted to this "extraction" sheet.

If you decide that this "course of action" is appropriate, let me know, and I will follow-up with VBA code which you can use for the extraction. I could also send you an example file. Or, better yet, you could FIRST send me a (SMALLER) example of your existing file, and I could then add on the required VBA code and return it to you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

I see that I was posting at the SAME time as Jamie Gillespie, and... that HIS solution appears to be THE BEST and EASIEST approach. My "hats off" to Jamie, and a STAR.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca