Sorry, I accidently sent my reply to the very wrong place… ---------- Forwarded message ---------- From: Johnny Rosenberg <[email protected]> Date: 2015-03-10 19:26 GMT+01:00 Subject: Re: [libreoffice-users] Calc formula ... To: [email protected]
Another idea would probably be to first enter the first formula on the seventh row, then the following formulas could check if the seventh cell above is a formula… For instance, the first formula in C7, than in C8: =if(isformula(C1);average(B2:B8);"") That cell could then be copied down as far as needed. Something like that… Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ 2015-03-10 12:49 GMT+01:00 Carl Paulsen <[email protected]>: > 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: [email protected] > 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 > > -- To unsubscribe e-mail to: [email protected] 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
