Home

minimum of every x-th cell

Highlighted
New Contributor

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

6 Replies
Highlighted

Hi,

 

That could be

=AGGREGATE(15,6,1/(MOD(ROW($K$8:$K$390),4)=0)/($K$8:$K$390<>0)*$K$8:$K$390,1)

Please see attached

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.

Highlighted

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.

Highlighted

I've add an attachment with my problem.

 

I don't know maybe its because I have really small numbers?

The ones I marked are the ones I want to determine the minimum values of.

 

I don't really know what this formular does, even after reading the blog about it.

I'm sorry I'm really new to the topics.

Highlighted
To add: when i insert the formular the output is #Name
I changed the , to ;
if I would have left the , there would be an error message like "an error in the formular without further explanaitions"
And the ;4 seems to a problem as well. But I'm not really sure about that.
Highlighted

Is it possible to attach your sample as Excel file, not as screenshot?

 

As for the #NAME try to open my file if it gives such error or not. Formulas in it shall be automatically translated into your locale notation.

Related Conversations
minimum of every x-th cell
LaConfiance in Excel on
6 Replies
X+1 +1 +1... till match a condition
Deleted in Excel on
7 Replies
not able to type number and text in same cell?
Deleted in Excel on
2 Replies