Subject: Re: [libreoffice-users] Calc formula ...

That cell could then be copied down as far as needed.

> Wow, excellent suggestions Brian. Your ideas are always spot on. Thanks.

> Carl

>

>

> On 3/10/15 4:08 AM, Brian Barker wrote:

>

>> At 19:20 10/03/2015 +1300, David Love wrote:

>>

>>> I have a three column spreadsheet.

>>> Column A. Date - The dates are consecutive

>>> Column B. Production - The Production has a daily target of 10,000 units

>>> and each production period is of seven days.

>>> Column C. a formula every seventh cell.

>>>

>>> Is it possible to construct a formula which will show in the seventh

>>> cell of column C the average for the number of days of production i.e. if

>>> day 1 reaches a production of 10,000 units I want this figure entered into

>>> the seventh cell in column C.

>>>

>>

>> Hold on! How do you know this is going to be the average for the week? Do

>> your workers celebrate reaching the daily target and take the rest of the

>> week off? Surely they need to attempt the same daily target on each of the

>> next six days? Or do you mean that 10000 is the *weekly* target? If so,

>> what happens when it is reached? Does production automatically stop to

>> prevent its being exceeded? Or could some weeks exceed 10000 - even by

>> accident?

>>

>> If days 1+2+3+4 total, say, 42,500 I want the seventh cell in column C

>>> to show the average of 10,265. In other words I want the average shown for

>>> the actual days of production each seven day period.

>>>

>>

>> So 10000 isn't a weekly limit. In that case, what is the significance of

>> the 10000? If four days exceed 10000, as here, it's not a daily limit

>> either: at least one of these days must have exceeded 10000. I'm beginning

>> to suspect that it has no significance for the calculation (so you didn't

>> need to tell us): it may be of interest only to the workers' supervisor in

>> interpreting the results.

>>

>> You can find the average of non-negative values (i.e. non-zero values in

>> your case, assuming production cannot be negative) by putting in, say, C7:

>> =AVERAGEIF(B1:B7;">0")

>> If you copy this and paste it into every seventh row of column C, you

>> will have what you need.

>>

>> But that leaves you with the rather messy requirement to paste separately

>> into every seventh row - a process very prone to error. Instead, in C7 try:

>> =IF(MOD(ROW();7)=0;AVERAGEIF(B1:B7;">0");"")

>> ROW() returns the current row number. The MOD() function returns the

>> remainder on dividing by 7. If this is zero - as it will be for row 7 and

>> every seventh row thereafter - the required average is shown; otherwise the

>> null string ensures that there is no display in the cell. You can copy or

>> fill this down column C without the same risk of error as the previous

>> suggestion. Note that comparing the result of the MOD() function with zero

>> will show results in rows 7, 14, 21, and so on. You will have to change the

>> "0" to "1" to show results instead in rows 8, 15, 22, and so on - and

>> similarly for other possibilities. Once you have found the appropriate

>> value, you can fill the formula containing it down the column.

>>

>> Is it possible for there to be no production at all in a particular week?

>> The above formula, in evaluating the average of no values, attempts to

>> divide by zero and displays #DIV/0! . You could test for this and avoid it

>> in various ways. If days with no production have empty cells in column B,

>> =IF(AND(COUNT(B1:B7)>0;MOD(ROW();7)=0);AVERAGEIF(B1:B7;">0");"")

>> would suffice. If they have (or may have) explicit zero values, try:

>> =IF(AND(COUNTIF(B1:B7;">0")>0;MOD(ROW();7)=0);AVERAGEIF(B1:B7;">0");"")

>>

>> I trust this helps.

>>

>> Brian Barker

>>

>>

>>

> --

>

> Carl Paulsen

>

> 8 Hamilton Street

>

> Dover, NH 03820

>

> (603) 749-2310

>

>

>

> --

> To unsubscribe e-mail to:

[hidden email]
> Problems?

http://www.libreoffice.org/get-help/mailing-lists/how-to-> unsubscribe/

> Posting guidelines + more:

http://wiki.documentfoundation.org/Netiquette> List archive:

http://listarchives.libreoffice.org/global/users/> All messages sent to this list will be publicly archived and cannot be

> deleted

>

>

All messages sent to this list will be publicly archived and cannot be deleted