• 411K Members
• 6,702 Online
• 466K 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

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

Please see attached

# Re: minimum of every x-th cell

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.

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

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

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

# 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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies