Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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

First week's formula

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

Other weeks' formula

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.

share|improve this answer
    
+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......

share|improve this answer

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:

sample spreadsheet with weeks entered as rows

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.