I have dataset include Zeros and I want Min result to be the number bigger than zero
I need to get the lowest price of a product and there are more than 200 person entering data , some of them entering zero by mistake,
and Min formula results Zero
—–
Ghassan
Use MINIFS. Look it up in the help.
It takes 3 parameters, a range for the minimum value, a range for the criteria, and the criteria. So if your dataset is column B, then you would use B for both your range and your criteria range since you are testing the same numbers for both the minimum value and whether that row should be included. Then use ">0" as the criteria, since you only want values larger than 0.
=MINIFS(B,B,">0")
that works if the dataset at one column ,
but I have data in different columns
Ghassan: You can use MINIFS on multiple cells. Read the details on MINIFS carefully and I'm sure you can figure something out.
=MINIFS(C2,D2,E2,F2,G2,H2,I2,C2,D2,E2,F2,G2,H2,I2,">0")
that what I used and I still get the result 0
Ghassan: To use multiple cells you need to define a range. You can't just keep adding your own parameters to the function. Look at the definition of MINIFS in the help in Numbers. Always study that closely before trying a new function.
MINIFS is =MINIFS(range, criteria-range, criteria). The range in your example is C2:I2. So:
=MINFIS(C2:I2,C2:I2,">0")
sorry for not explaining this well ,
what I want to know is how to use this formula if I have multiple cells and there are cells that I do not want in the range.
Example
=MINIFS(D2,F2,H2,J2,D2,F2,H2,J2,">0") the result here 0!
here I don't want cell E2 , G2 , I2
Ghassan: That's not the way to use the MINIFS function. Please read the help for MINIFS in Numbers. Look at the description, look at the examples.
=MINFIS(C2:I2,C2:I2,">0") will give you the lowest number greater than 0. If the cells E2, G2 and I2 contain nothing, or 0 or some text, the should be ignored. TRY IT.
If those gap cells have numbers in them, then they would be included. It is not clear from your comments what is in those cells and why they are skipped. If you really need to skip them, you can use UNION.RANGES to create a non-contiguous range.
=MINIFS(UNION.RANGES(FALSE,D2,F2,H2,J2),UNION.RANGES(FALSE,D2,F2,H2,J2),">0")
Look up UNION.RANGES and please read about it so you understand what you are doing here.