• 589K Members
• 6,076 Online
• 714K Conversations

New Contributor

# 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

6 Replies
Highlighted

# Re: minimum of every x-th cell

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)`

Highlighted

# Re: minimum of every x-th cell

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

# Re: minimum of every x-th cell

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

# Re: minimum of every x-th cell

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

# Re: minimum of every x-th cell

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

# Re: minimum of every x-th cell

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
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