Question description:
This user has given permission to use the problem statement for this
blog.
I need an IF statement embedded in a MIN statement to avoid the #DIV/0 return. =(MIN(1,N26/I26))
Solved by K. H. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/07/2018 - 08:16
Hi
User
23/07/2018 - 08:16
Hi
Excelchat Expert
23/07/2018 - 08:16
Welcome to Got It Pro.
User
23/07/2018 - 08:17
Is there a way to embed IF in a MIN statement?
Excelchat Expert
23/07/2018 - 08:17
You can include iferror to avoid Div/0 error.
Excelchat Expert
23/07/2018 - 08:17
iferror function.
User
23/07/2018 - 08:17
Here is the formula I am having issues with =(MIN(1,N26/I26))
User
23/07/2018 - 08:18
I do not know how to enter that function
Excelchat Expert
23/07/2018 - 08:19
Ok. What is the result you are going to expect to avoid the error? Shall i make it as zero?
User
23/07/2018 - 08:19
Yes please
Excelchat Expert
23/07/2018 - 08:19
=iferror(MIN(1,N26/I26),0)
Excelchat Expert
23/07/2018 - 08:19
=iferror(formula,value when error comes).
Excelchat Expert
23/07/2018 - 08:20
The above is the syntax.
User
23/07/2018 - 08:22
Is there a way to get it to calculate if there's a value and then chose zero if not. This is only working if the data is zero. I am looking to add it to a speadsheet where values may eventually get filled in
Excelchat Expert
23/07/2018 - 08:22
That is how it will calculate.
Excelchat Expert
23/07/2018 - 08:22
If there is no error, it calculate the original value.
Excelchat Expert
23/07/2018 - 08:23
If there is an error, then it retuns the value in the second argument.
User
23/07/2018 - 08:23
ok so I can't paste it and lock the cell awaiting data correct?
Excelchat Expert
23/07/2018 - 08:24
Sorry, i did not get your message. You want to replace the error with zero?
User
23/07/2018 - 08:25
yes, but then should there be value in the cell I want it to calculate
Excelchat Expert
23/07/2018 - 08:26
Yes. it calculate the value. In the preview, i don't have values for N26 and I26. If there is some value, it calculates it. I will enter example values.
User
23/07/2018 - 08:27
It didn;t work when I pasted in the example. I have several fields returning errors
Excelchat Expert
23/07/2018 - 08:27
Please check the yellow highlighted cell.
Excelchat Expert
23/07/2018 - 08:27
Please check the reference cells.
Excelchat Expert
23/07/2018 - 08:28
In the provided formula, you have to enter value in N26 and I26.
Excelchat Expert
23/07/2018 - 08:28
If it is a different cell, please adjust it.
User
23/07/2018 - 08:30
The other cells are IF Statements, I believe they just need a secondary statement correct
User
23/07/2018 - 08:30
See N6
Excelchat Expert
23/07/2018 - 08:31
Yes. You can use iferror function here as well.
Excelchat Expert
23/07/2018 - 08:31
=iferror(if formula,0)
Excelchat Expert
23/07/2018 - 08:31
=iferror(IF(N185>J185,1,(N185/J185)),0)
Excelchat Expert
23/07/2018 - 08:31
Please refer the above formula to apply iferror.
User
23/07/2018 - 08:32
=iferror(IF(N185>J185,1,(N185/J185))
Excelchat Expert
23/07/2018 - 08:32
Please use the second argument, as stated above.
Excelchat Expert
23/07/2018 - 08:32
=iferror(IF(N185>J185,1,(N185/J185)),0)
User
23/07/2018 - 08:33
=(((O185*F185)*(P185=0))+((P185*F185)*(P185>0)))
User
23/07/2018 - 08:33
ok I think I got it
Excelchat Expert
23/07/2018 - 08:33
=iferror(formula,0)
Excelchat Expert
23/07/2018 - 08:33
formula can be any formulas.
User
23/07/2018 - 08:35
The last formula I sent is giving an erro
User
23/07/2018 - 08:35
still
Excelchat Expert
23/07/2018 - 08:35
P185=0?
Excelchat Expert
23/07/2018 - 08:36
Please check this portion in your formula.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
