# How do you find the maximum value in a column, skipping to every nth value?

Using LibreOffice's Calc program (nearly the same functionality as Microsoft Excel) I have a set of numbers in column T, which represent numbers for the week (with labels in S). Each week's worth of data is 3 rows, then a blank row separating it from the next week:

``````S |  T
=======

A | 90
B | 180
C | 250

A | 95
B | 160
C | 255

A | 100
B | 175
C | 260

A | 95
B | 185
C | 230
``````

Is there a way to calculate the maximum value up until a cell, skipping to every nth row? The data in the other columns for each week and number group is based off the previous week's data in column T, but I would like to use the highest value to date.

Using the data above as an example: for weeks 1, 2, and 3, I would like the B formula to return 180, but on week 4 (where there is a higher number), 185. For the A group, week 1 would be base on 90, then 95 for week 2, 100 for week 3, but then 100 again for week 4.

-

You can just keep comparing the current week's value with the previous week's maximum value.

For the first week, use a formula of the pattern `=`this week's value

For every other week use a formula `=MAX(`this week's value`,`last week's maximum`)`

Because the relative positions are the same as you go down, you can copy this formula to all the other cells. In this example, you can select one blank cell and three cells with formulas, copy, then select the next blank cell to the final cell you want to contain a formula, then paste.

-
+1 Nice elegant solution! – barry houdini Sep 6 '12 at 9:02

In Excel you could use an "array formula" like this to get max value for "A"

`=MAX(IF(A1:A100="A",B1:B100))`

confirmed with CTRL+SHIFT+ENTER

AS you add more data for each week the max value would update

I don't know whether this works in libreoffice......

-

Do you need tto have your data organized the way you have shown?  This becomes simple (in Excel 2007) if you put all the data for each week in the same row:

-