Forum Discussion

LaConfiance's avatar
LaConfiance
Copper Contributor
Jan 29, 2019

minimum of every x-th cell

Hello everyone,

I'm kind of new to working professional with excel. I'm trying to figure out how to do:
Searching the Minimum value of line (K8:K320) of every fourth cell, but without the value 0.

I did the same with Maximum which (I don't know why, but doesn't work as well, but sometimes it does):

{=MAX(WENN(REST(ZEILE(K8:K390);4);(K8:K390)))}

 

and for min: {=MIN(WENN(REST(ZEILE(K9:K391);4)*0;(K9:K391)))} and it did not want to work the way I want it to :D.
I know it has something to do with the "*0" but I don't know how to exclude 0 as a value otherwise.

 

I hope you understand what my problem is.

Nevertheless I hope you have a nice day

 

Greets
Lisa

    • LaConfiance's avatar
      LaConfiance
      Copper Contributor

      thanks for your fast reply.

      Sadly it doesn't work.

      And I don't quiet get the part with the 15,6,1?

      may you help me again :)?

      The error message say there's a mistake in the formular.

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        Did you check the file attached to my previous post? Within it formula works, no errors.

         

        If it is an error in your case please give more detail which one, or even better attach small sample file.

         

        Parameters for AGGREGATE are described here https://support.office.com/en-us/article/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df. We find first (very last 1) small (15) value ignoring all errors in calculation (6), filtering our range with 1/(..) works as a filter returning errors for the cells which are not met condition.

Resources