But can u give me explanation plzI will be glad to u
But can u give me explanation plzI will be glad to u
=IFERROR(1/(1/MIN(IF(A1:A14>B1,A1:A14))),"")
If there are no numbers greater than the lookup value then the MIN function will return 0.
You don't want a result of 0 so I intentionally wrote the formula so that it will cause an error to be generated if there are no numbers greater than the lookup value. But, we then use the IFERROR function to trap that error.
We can say that 1 divided by 1 divided by some number will equal that number UNLESS that number is 0. When the number is 0 the division operations will result in the #DIV/0! error.
For example:
If the result of:
MIN(IF(A1:A14>B1,A1:A14)) = 3.25
Then:
1/(1/MIN(IF(A1:A14>B1,A1:A14))) = 3.25
Then:
=IFERROR(3.25,"") = 3.25
However, if there are no numbers greater than the lookup value the result of:
MIN(IF(A1:A14>B1,A1:A14)) = 0
Then:
1/(1/MIN(IF(A1:A14>B1,A1:A14))) = #DIV/0!
Then:
=IFERROR(#DIV/0!,"") = "" (blank)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks