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

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: users+unsubscribe@global.libreoffice.org
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