I want to calculate the minimum value but exclude all zeros.
I tried previously suggested solutions, but it is still not working. Any ideas?
Create a line item (List formatted on Client) - Line item 1 -> IF amount = 0 then blank else item(clients)
Line item 2 -> Amount[MIN: Line item 1]
Hope that helps
You need to provide more info on your query. There are a lot of open ends and need more clarity.
However, There is a MIN function that you can use as an aggregation function.
The minimum summary method does not work for me because it brings back zeros, which makes sense. I want the calculation to take into consideration the minimum of the highlighted non-zero numbers only. I tried IF statements, but since it ends with Else 0, I am still getting zeros.
Here is the implementation of @Misbah 's solution. Using that method you'll have "Infinity" in some cells. I have added two additional line items as solution 2. You can also have a look at those.
Both solutions work fine.
I think you can use optimizer for this. Excluding zeros and getting minimum value.
Thank you so much for your timely solutions @Misbah, @ManjunathKN , and @ShubhamCh . The only issue is getting rid of the infinity, but this is good for now.
@ManjunathKN , can you expand on that? Any pictures or examples you can share?
@ymugeni if above solution helps then very well. You may go through the optimizer lesson, they have explained there.
In the screenshot shared by me, I have added 2 independent line items as a different solution. You can also use that solution. It won't show "Infinity" in the cells. The second line item - solution2 L2 is optional. You want to use it, use it otherwise use solution2 L1's summary (Min).
Ok, will do. Thanks.
Just as a comment to this as other people may view this article.
I have used another approach to this in the past.
Given you have a scenario where you want to view zero if that is the only entry (or if there are no number entries). But this needs a few more steps:
What I have done then is to set up the following function
Numbers to review.————— (blank or your equation). summery setting MAX
Max Value —————————-Numbers to review ← Dim by parent
Zero Value? (Boolean).————'numbers to review' = 0
New Number to review.————if zero value? then max value[lookup parent] else numbers to review. Summery -Setting: Min
Min Value & avoid Zero. ———'New number to review.' ← Dim by parent
This would only generate 0 if all items where 0 else it would return the smallest value <> 0