Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Workbook containing two sheets
Sheet1 = trips Some cells in ColumnB is blank (0) ColumnA ColumnB Mon, Jan 08, 2007 Mon, Jan 08, 2007 Mon, Jan 08, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 $77.50 Thu, Jan 11, 2007 Fri, Jan 12, 2007 Fri, Jan 12, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Mon, Jan 15, 2007 $27.50 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Wed, Jan 17, 2007 Wed, Jan 17, 2007 $123.75 Wed, Jan 17, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Fri, Jan 19, 2007 Fri, Jan 19, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sheet2 = average Formula which generating Date from Sheet1-ColumnA as one Date in cells Sheet2- ColumnA: {=IF(ISNUMBER(SMALL(IF(trips!$A$3:$A$1201<trips!$ A$2:$A$1200,ROW(trips!$A$2: $A$1200)-ROW(trips!$A$2)+1),ROW()-ROW($A$2)+1)),INDEX(trips!$A$2:$A$1200, SMALL(IF(trips!$A$3:$A$1201<trips!$A$2:$A$1200,RO W(trips!$A$2:$A$1200)-ROW (trips!$A$2)+1),ROW()-ROW($A$2)+1)),"")} array formula Cells in ColumnB containing formula {=AVERAGE(IF(trips!$A$2:$A$1200=DATE(2007,1,1),IF (trips!$A$2:$A$1200<=A2,IF (trips!$B$2:$B$1200<"",trips!$B$2:$B$1200))))} array formula DATE(2007,1,1) start date A2 date in Sheet2=average ColumnA A3 A4 ............. and so on Which is working fine (doing average by date). Only two problems in this formula. Problems: 1. Error message (#DIV/0!) if cells in Sheet1 ColumnB is empty. (In this situation they empty till January,10) 2. Continuing show same last value if no date in Sheet2 ColumnA. ColumnA ColumnB Mon, January 08, 2007 #DIV/0!-----------Problem #1 Tue, January 09, 2007 #DIV/0!-----------Problem #1 Wed, January 10, 2007 #DIV/0!-----------Problem #1 Thu, January 11, 2007 $77.50 Fri, January 12, 2007 $77.50 Sun, January 14, 2007 $77.50 Mon, January 15, 2007 $52.50 Tue, January 16, 2007 $52.50 Wed, January 17, 2007 $76.25 Thu, January 18, 2007 $76.25 Fri, January 19, 2007 $76.25 Sun, January 21, 2007 $76.25 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 and so on Problem #1 need to be blank Problem #2 need to be blank PLEASE! ANY SUGGESTIONS. Thanks in advance. Sincerely, Igor (inta251). -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
example B1: blank or 0, and C1: 9
If you take D1 =C1/B1 (if B1 = 0 or blank cell it will give you #DIV/0! error) to trap this #DIV/0! error D1 =IF(OR(B1="",B1=0),"",C1/B1) or D1 =IF(ISERROR(C1/B1),"",C1/B1) Adjust to suit your needed "inta251 via OfficeKB.com" wrote: Workbook containing two sheets Sheet1 = trips Some cells in ColumnB is blank (0) ColumnA ColumnB Mon, Jan 08, 2007 Mon, Jan 08, 2007 Mon, Jan 08, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 $77.50 Thu, Jan 11, 2007 Fri, Jan 12, 2007 Fri, Jan 12, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Mon, Jan 15, 2007 $27.50 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Wed, Jan 17, 2007 Wed, Jan 17, 2007 $123.75 Wed, Jan 17, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Fri, Jan 19, 2007 Fri, Jan 19, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sheet2 = average Formula which generating Date from Sheet1-ColumnA as one Date in cells Sheet2- ColumnA: {=IF(ISNUMBER(SMALL(IF(trips!$A$3:$A$1201<trips!$ A$2:$A$1200,ROW(trips!$A$2: $A$1200)-ROW(trips!$A$2)+1),ROW()-ROW($A$2)+1)),INDEX(trips!$A$2:$A$1200, SMALL(IF(trips!$A$3:$A$1201<trips!$A$2:$A$1200,RO W(trips!$A$2:$A$1200)-ROW (trips!$A$2)+1),ROW()-ROW($A$2)+1)),"")} array formula Cells in ColumnB containing formula {=AVERAGE(IF(trips!$A$2:$A$1200=DATE(2007,1,1),IF (trips!$A$2:$A$1200<=A2,IF (trips!$B$2:$B$1200<"",trips!$B$2:$B$1200))))} array formula DATE(2007,1,1) start date A2 date in Sheet2=average ColumnA A3 A4 ............. and so on Which is working fine (doing average by date). Only two problems in this formula. Problems: 1. Error message (#DIV/0!) if cells in Sheet1 ColumnB is empty. (In this situation they empty till January,10) 2. Continuing show same last value if no date in Sheet2 ColumnA. ColumnA ColumnB Mon, January 08, 2007 #DIV/0!-----------Problem #1 Tue, January 09, 2007 #DIV/0!-----------Problem #1 Wed, January 10, 2007 #DIV/0!-----------Problem #1 Thu, January 11, 2007 $77.50 Fri, January 12, 2007 $77.50 Sun, January 14, 2007 $77.50 Mon, January 15, 2007 $52.50 Tue, January 16, 2007 $52.50 Wed, January 17, 2007 $76.25 Thu, January 18, 2007 $76.25 Fri, January 19, 2007 $76.25 Sun, January 21, 2007 $76.25 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 and so on Problem #1 need to be blank Problem #2 need to be blank PLEASE! ANY SUGGESTIONS. Thanks in advance. Sincerely, Igor (inta251). -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Try this:
Create these defined names: InsertNameDefine Name: Rng Refers to: =Trips!$B$2:INDEX(Trips!$A$2:$A$65536,MATCH(10^10, Trips!$A$2:$A$65536)) Name: Avg Refers to: =AVERAGE(IF(INDEX(rng,,1)=DATE(2007,1,1),IF(INDEX (rng,,1)<=Sheet2!$A2,IF(INDEX(rng,,2)<"",INDEX(rn g,,2))))) Enter these formulas on Sheet2: A1: =SUMPRODUCT((INDEX(rng,,1)<"")/COUNTIF(INDEX(rng,,1),INDEX(rng,,1)&"")) A2: =IF(ROWS($1:1)<=A$1,INDEX(rng,SMALL(IF(FREQUENCY(I NDEX(rng,,1),INDEX(rng,,1))0,ROW(rng)-1),ROWS($1:1)),,1),"") B2: =IF(A2="","",IF(ISERROR(avg),"",avg)) Select both A2 and B2 and copy down as needed. Format A2:An as DATE Biff "inta251 via OfficeKB.com" <u30987@uwe wrote in message news:6d9552d226fea@uwe... Workbook containing two sheets Sheet1 = trips Some cells in ColumnB is blank (0) ColumnA ColumnB Mon, Jan 08, 2007 Mon, Jan 08, 2007 Mon, Jan 08, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 $77.50 Thu, Jan 11, 2007 Fri, Jan 12, 2007 Fri, Jan 12, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Mon, Jan 15, 2007 $27.50 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Wed, Jan 17, 2007 Wed, Jan 17, 2007 $123.75 Wed, Jan 17, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Fri, Jan 19, 2007 Fri, Jan 19, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sheet2 = average Formula which generating Date from Sheet1-ColumnA as one Date in cells Sheet2- ColumnA: {=IF(ISNUMBER(SMALL(IF(trips!$A$3:$A$1201<trips!$ A$2:$A$1200,ROW(trips!$A$2: $A$1200)-ROW(trips!$A$2)+1),ROW()-ROW($A$2)+1)),INDEX(trips!$A$2:$A$1200, SMALL(IF(trips!$A$3:$A$1201<trips!$A$2:$A$1200,RO W(trips!$A$2:$A$1200)-ROW (trips!$A$2)+1),ROW()-ROW($A$2)+1)),"")} array formula Cells in ColumnB containing formula {=AVERAGE(IF(trips!$A$2:$A$1200=DATE(2007,1,1),IF (trips!$A$2:$A$1200<=A2,IF (trips!$B$2:$B$1200<"",trips!$B$2:$B$1200))))} array formula DATE(2007,1,1) start date A2 date in Sheet2=average ColumnA A3 A4 ............ and so on Which is working fine (doing average by date). Only two problems in this formula. Problems: 1. Error message (#DIV/0!) if cells in Sheet1 ColumnB is empty. (In this situation they empty till January,10) 2. Continuing show same last value if no date in Sheet2 ColumnA. ColumnA ColumnB Mon, January 08, 2007 #DIV/0!-----------Problem #1 Tue, January 09, 2007 #DIV/0!-----------Problem #1 Wed, January 10, 2007 #DIV/0!-----------Problem #1 Thu, January 11, 2007 $77.50 Fri, January 12, 2007 $77.50 Sun, January 14, 2007 $77.50 Mon, January 15, 2007 $52.50 Tue, January 16, 2007 $52.50 Wed, January 17, 2007 $76.25 Thu, January 18, 2007 $76.25 Fri, January 19, 2007 $76.25 Sun, January 21, 2007 $76.25 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 and so on Problem #1 need to be blank Problem #2 need to be blank PLEASE! ANY SUGGESTIONS. Thanks in advance. Sincerely, Igor (inta251). -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Thanks for quick respond.
I try both ways. Same result. Teethless mama wrote: example B1: blank or 0, and C1: 9 If you take D1 =C1/B1 (if B1 = 0 or blank cell it will give you #DIV/0! error) to trap this #DIV/0! error D1 =IF(OR(B1="",B1=0),"",C1/B1) or D1 =IF(ISERROR(C1/B1),"",C1/B1) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Here's a refined method.
Since what you're doing is extracting the unique dates and the dates are in ascending order: InsertNameDefine Name: Rng Refers to: =Trips!$B$2:INDEX(Trips!$A$2:$A$65536,MATCH(10^10, Trips!$A$2:$A$65536)) You can use a more reasonably sized range in the name. Like A2:A1500. I'm assuming you need a dynamic range based on how I'm "reading" your post. Enter these formulas on Sheet2: A1: =SUMPRODUCT((INDEX(rng,,1)<"")/COUNTIF(INDEX(rng,,1),INDEX(rng,,1)&"")) A2: normally entered, not an array: =MIN(INDEX(rng,,1)) A3: array entered and copied down: =IF(ROWS($1:2)<=A$1,MIN(IF(INDEX(rng,,1)A2,INDEX( rng,,1))),"") Format A2:An as DATE The formula for column B is the same. Biff "T. Valko" wrote in message ... Try this: Create these defined names: InsertNameDefine Name: Rng Refers to: =Trips!$B$2:INDEX(Trips!$A$2:$A$65536,MATCH(10^10, Trips!$A$2:$A$65536)) Name: Avg Refers to: =AVERAGE(IF(INDEX(rng,,1)=DATE(2007,1,1),IF(INDEX (rng,,1)<=Sheet2!$A2,IF(INDEX(rng,,2)<"",INDEX(rn g,,2))))) Enter these formulas on Sheet2: A1: =SUMPRODUCT((INDEX(rng,,1)<"")/COUNTIF(INDEX(rng,,1),INDEX(rng,,1)&"")) A2: =IF(ROWS($1:1)<=A$1,INDEX(rng,SMALL(IF(FREQUENCY(I NDEX(rng,,1),INDEX(rng,,1))0,ROW(rng)-1),ROWS($1:1)),,1),"") B2: =IF(A2="","",IF(ISERROR(avg),"",avg)) Select both A2 and B2 and copy down as needed. Format A2:An as DATE Biff "inta251 via OfficeKB.com" <u30987@uwe wrote in message news:6d9552d226fea@uwe... Workbook containing two sheets Sheet1 = trips Some cells in ColumnB is blank (0) ColumnA ColumnB Mon, Jan 08, 2007 Mon, Jan 08, 2007 Mon, Jan 08, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 $77.50 Thu, Jan 11, 2007 Fri, Jan 12, 2007 Fri, Jan 12, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Mon, Jan 15, 2007 $27.50 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Wed, Jan 17, 2007 Wed, Jan 17, 2007 $123.75 Wed, Jan 17, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Fri, Jan 19, 2007 Fri, Jan 19, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sheet2 = average Formula which generating Date from Sheet1-ColumnA as one Date in cells Sheet2- ColumnA: {=IF(ISNUMBER(SMALL(IF(trips!$A$3:$A$1201<trips!$ A$2:$A$1200,ROW(trips!$A$2: $A$1200)-ROW(trips!$A$2)+1),ROW()-ROW($A$2)+1)),INDEX(trips!$A$2:$A$1200, SMALL(IF(trips!$A$3:$A$1201<trips!$A$2:$A$1200,RO W(trips!$A$2:$A$1200)-ROW (trips!$A$2)+1),ROW()-ROW($A$2)+1)),"")} array formula Cells in ColumnB containing formula {=AVERAGE(IF(trips!$A$2:$A$1200=DATE(2007,1,1),IF (trips!$A$2:$A$1200<=A2,IF (trips!$B$2:$B$1200<"",trips!$B$2:$B$1200))))} array formula DATE(2007,1,1) start date A2 date in Sheet2=average ColumnA A3 A4 ............ and so on Which is working fine (doing average by date). Only two problems in this formula. Problems: 1. Error message (#DIV/0!) if cells in Sheet1 ColumnB is empty. (In this situation they empty till January,10) 2. Continuing show same last value if no date in Sheet2 ColumnA. ColumnA ColumnB Mon, January 08, 2007 #DIV/0!-----------Problem #1 Tue, January 09, 2007 #DIV/0!-----------Problem #1 Wed, January 10, 2007 #DIV/0!-----------Problem #1 Thu, January 11, 2007 $77.50 Fri, January 12, 2007 $77.50 Sun, January 14, 2007 $77.50 Mon, January 15, 2007 $52.50 Tue, January 16, 2007 $52.50 Wed, January 17, 2007 $76.25 Thu, January 18, 2007 $76.25 Fri, January 19, 2007 $76.25 Sun, January 21, 2007 $76.25 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 and so on Problem #1 need to be blank Problem #2 need to be blank PLEASE! ANY SUGGESTIONS. Thanks in advance. Sincerely, Igor (inta251). -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Maybe something like this:
With... On a sheet named Trips A1: MyDate A2:A100 dates or blanks B1: Amount B2:B100 amounts across from dates...or blanks and On sheet named Avg A1: MyDate Put this ARRAY FORMULA (committed with Ctrl+Shift+Enter) in A2: =IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$100,trips!$A$2:$A$100)0) ),SMALL(IF(ISNUMBER(trips!$A$2:$A$101),IF(ROW(trip s!$A$2:$A$101)=MATCH(trips!$A$2:$A$101,trips!$A$2: $A$101,0)+1,trips!$A$2:$A$101)),ROW()-1),"") Copy that formula down as far as you need B1: Average Put this regular formula in B2: =IF(SUMIF(trips!$A$2:$A$100,avg!$A2,trips!$B$2:$B$ 100),AVERAGE(trips!$B$2:INDEX(trips!$B:$B,MATCH(A2 +1,trips!$A$2:$A$100,1)-1)),"") Copy that formula down as far as you need With your posted data, those formula return this: MyDate Average 01/08/07 01/09/07 01/10/07 01/11/07 77.5 01/12/07 01/14/07 01/15/07 52.5 01/16/07 01/17/07 76.25 01/18/07 01/19/07 01/21/07 Does that help? *********** Regards, Ron XL2002, WinXP "inta251 via OfficeKB.com" wrote: Workbook containing two sheets Sheet1 = trips Some cells in ColumnB is blank (0) ColumnA ColumnB Mon, Jan 08, 2007 Mon, Jan 08, 2007 Mon, Jan 08, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 $77.50 Thu, Jan 11, 2007 Fri, Jan 12, 2007 Fri, Jan 12, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Mon, Jan 15, 2007 $27.50 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Wed, Jan 17, 2007 Wed, Jan 17, 2007 $123.75 Wed, Jan 17, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Fri, Jan 19, 2007 Fri, Jan 19, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sheet2 = average Formula which generating Date from Sheet1-ColumnA as one Date in cells Sheet2- ColumnA: {=IF(ISNUMBER(SMALL(IF(trips!$A$3:$A$1201<trips!$ A$2:$A$1200,ROW(trips!$A$2: $A$1200)-ROW(trips!$A$2)+1),ROW()-ROW($A$2)+1)),INDEX(trips!$A$2:$A$1200, SMALL(IF(trips!$A$3:$A$1201<trips!$A$2:$A$1200,RO W(trips!$A$2:$A$1200)-ROW (trips!$A$2)+1),ROW()-ROW($A$2)+1)),"")} array formula Cells in ColumnB containing formula {=AVERAGE(IF(trips!$A$2:$A$1200=DATE(2007,1,1),IF (trips!$A$2:$A$1200<=A2,IF (trips!$B$2:$B$1200<"",trips!$B$2:$B$1200))))} array formula DATE(2007,1,1) start date A2 date in Sheet2=average ColumnA A3 A4 ............. and so on Which is working fine (doing average by date). Only two problems in this formula. Problems: 1. Error message (#DIV/0!) if cells in Sheet1 ColumnB is empty. (In this situation they empty till January,10) 2. Continuing show same last value if no date in Sheet2 ColumnA. ColumnA ColumnB Mon, January 08, 2007 #DIV/0!-----------Problem #1 Tue, January 09, 2007 #DIV/0!-----------Problem #1 Wed, January 10, 2007 #DIV/0!-----------Problem #1 Thu, January 11, 2007 $77.50 Fri, January 12, 2007 $77.50 Sun, January 14, 2007 $77.50 Mon, January 15, 2007 $52.50 Tue, January 16, 2007 $52.50 Wed, January 17, 2007 $76.25 Thu, January 18, 2007 $76.25 Fri, January 19, 2007 $76.25 Sun, January 21, 2007 $76.25 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 (blank)waiting for date from Sheet1 $76.25-----------Problem #2 and so on Problem #1 need to be blank Problem #2 need to be blank PLEASE! ANY SUGGESTIONS. Thanks in advance. Sincerely, Igor (inta251). -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Thanks for reapply.
Before to put all this formulas to real workbook, I decided create new workbook. Working 100% PLUS. This is what I was looking for. Sheet1=trips InsertNameDefine Name: Rng Refers to: =trips!$B$2:INDEX(trips!$A$2:$A$1200,MATCH(10^10,t rips!$A$2:$A$1200)) Name: Avg Refers to: =AVERAGE(IF(INDEX(rng,,1)=DATE(2007,1,1),IF(INDEX (rng,,1)<=average!$A2,IF (INDEX(rng,,2)<"", INDEX(rng,,2))))) Sheet2=average A1: =SUMPRODUCT((INDEX(rng,,1)<"")/COUNTIF(INDEX(rng,,1),INDEX(rng,,1)&"")) A2: normally entered, not an array: =MIN(INDEX(rng,,1)) A3: array entered and copied down: =IF(ROWS($1:2)<=A$1,MIN(IF(INDEX(rng,,1)A2,INDEX( rng,,1))),"") Format A2:An as DATE The formula for column B is the same. BUT!!!!!!!!! When I put all this formulas to my real workbook and did some adjustments Sheet2 Column B STOP working as need it. I believe that because in the real workbook data located in Column K. What do I need adjust to correct this problem? Thanks again for your time. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Thanks for respond Ron.
All this formulas working fine, but with 2 problems. Problem 1: With my posted data I need result MyDate Average 01/08/07 blank (because no data available yet) 01/09/07 blank (because no data available yet) 01/10/07 blank (because no data available yet) 01/11/07 $77.50 01/12/07 $77.50 01/14/07 $77.50 01/15/07 $52.50 01/16/07 $52.50 01/17/07 $76.25 01/18/07 $76.25 01/19/07 $76.25 01/21/07 $76.25 And so on. Problem 2: If I have to calculate average from different column, where each cell has $ amount it giving me wrong result. Also, each cell where weekday Friday giving #N/A result. But date and data on that weekday available. Thanks again for your time. Sincerely, Igor (inta251) -- Message posted via http://www.officekb.com |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
in the real workbook data located in Column K
Why didn't you tell us that to begin with? Tell us *exactly* where your data is. Where *exactly* are the dates and where *exactly* are the dollar amounts? Biff "inta251 via OfficeKB.com" <u30987@uwe wrote in message news:6da2fbcd3a015@uwe... Thanks for reapply. Before to put all this formulas to real workbook, I decided create new workbook. Working 100% PLUS. This is what I was looking for. Sheet1=trips InsertNameDefine Name: Rng Refers to: =trips!$B$2:INDEX(trips!$A$2:$A$1200,MATCH(10^10,t rips!$A$2:$A$1200)) Name: Avg Refers to: =AVERAGE(IF(INDEX(rng,,1)=DATE(2007,1,1),IF(INDEX (rng,,1)<=average!$A2,IF (INDEX(rng,,2)<"", INDEX(rng,,2))))) Sheet2=average A1: =SUMPRODUCT((INDEX(rng,,1)<"")/COUNTIF(INDEX(rng,,1),INDEX(rng,,1)&"")) A2: normally entered, not an array: =MIN(INDEX(rng,,1)) A3: array entered and copied down: =IF(ROWS($1:2)<=A$1,MIN(IF(INDEX(rng,,1)A2,INDEX( rng,,1))),"") Format A2:An as DATE The formula for column B is the same. BUT!!!!!!!!! When I put all this formulas to my real workbook and did some adjustments Sheet2 Column B STOP working as need it. I believe that because in the real workbook data located in Column K. What do I need adjust to correct this problem? Thanks again for your time. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Is there anything else we should know about your situation?
*********** Regards, Ron XL2002, WinXP "inta251 via OfficeKB.com" wrote: Thanks for respond Ron. All this formulas working fine, but with 2 problems. Problem 1: With my posted data I need result MyDate Average 01/08/07 blank (because no data available yet) 01/09/07 blank (because no data available yet) 01/10/07 blank (because no data available yet) 01/11/07 $77.50 01/12/07 $77.50 01/14/07 $77.50 01/15/07 $52.50 01/16/07 $52.50 01/17/07 $76.25 01/18/07 $76.25 01/19/07 $76.25 01/21/07 $76.25 And so on. Problem 2: If I have to calculate average from different column, where each cell has $ amount it giving me wrong result. Also, each cell where weekday Friday giving #N/A result. But date and data on that weekday available. Thanks again for your time. Sincerely, Igor (inta251) -- Message posted via http://www.officekb.com |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Sorry guys. Please accept my apology.
Im not PRO, that why all this confuse. I was thinking from one formula Ill adjust for all columns which need it. Now I see that is not so easy. Now you know I need create average by days. Sheet1 = trips Column A = Days From Columns F to U $$$ amount, some cells will be empty. Sheet2 = average Column A = Days Formula for each cell in Column A {=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$1200, trips!$A$2:$A$1200)0)),SMALL(IF(ISNUMBER(trips!$A $2:$A$1201), IF(ROW(trips!$A$2:$A$1201)=MATCH(trips!$A$2:$A$120 1, trips!$A$2:$A$1201,0)+1,trips!$A$2:$A$1201)),ROW()-1),"")} which generating one day from list of same days. By the way this formula better than I had before. Column B row 2 need formula which will be calculate average from Sheet1 = trips Column F and so on. Sheet1 Sheet2 Col F -------------Col B Col G----------- Col C And so on, till Col U--------------Col Q Need result as posted above MyDate Average 01/08/07 blank (because no data available) 01/09/07 blank (because no data available) 01/10/07 blank (because no data available) 01/11/07 $77.50 01/12/07 $77.50 01/14/07 $77.50 01/15/07 $52.50 01/16/07 $52.50 01/17/07 $76.25 01/18/07 $76.25 01/19/07 $76.25 01/21/07 $76.25 And so on. Once again, THANKS for your time. Sincerely, Igor (inta251) Why didn't you tell us that to begin with? Tell us *exactly* where your data is. Where *exactly* are the dates and where *exactly* are the dollar amounts? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Sorry guys. Please accept my apology.
Im not PRO, that why all this confuse. I was thinking from one formula Ill adjust for all columns which need it. Now I see that is not so easy. Now you know I need create average by days. Sheet1 = trips Column A = Days From Columns F to U $$$ amount, some cells will be empty. Sheet2 = average Column A = Days Formula for each cell in Column A {=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$1200, trips!$A$2:$A$1200)0)),SMALL(IF(ISNUMBER(trips!$A $2:$A$1201), IF(ROW(trips!$A$2:$A$1201)=MATCH(trips!$A$2:$A$120 1, trips!$A$2:$A$1201,0)+1,trips!$A$2:$A$1201)),ROW()-1),"")} which generating one day from list of same days. By the way this formula better than I had before. Column B row 2 need formula which will be calculate average from Sheet1 = trips Column F and so on. Sheet1 Sheet2 Col F -------------Col B Col G----------- Col C And so on, till Col U--------------Col Q Need result as posted above MyDate Average 01/08/07 #DIV/0! 01/09/07 #DIV/0! 01/10/07 #DIV/0! 01/11/07 $77.50 01/12/07 $77.50 01/14/07 $77.50 01/15/07 $52.50 01/16/07 $52.50 01/17/07 $76.25 01/18/07 $76.25 01/19/07 $76.25 01/21/07 $76.25 blank $76.25 blank $76.25 blank $76.25 And so on. Once again, THANKS for your time. Sincerely, Igor (inta251) Currently i have formula which giving me correct result {=AVERAGE(IF((trips!$A$2:$A$1200=DATE(2007,1,1))* (trips!$A$2:$A$1200<=$A2), IF(trips!$K$2:$K$1200=0,"",trips!$K$2:$K$1200)))} If possible to add something to clean error #DIV/0! And $76.25 if no new date -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Try this regular formula:
=IF(OR(AND($A2<"",ISNUMBER($B1)),SUMIF(Trips!$A$2 :$A$1201,$A2,Trips!$B$2:$B$1201)),AVERAGE(Trips!$B $2:INDEX(Trips!$B:$B,MATCH(A2+1,Trips!$A$2:$A$1201 ,1)-1)),"") Does that help? *********** Regards, Ron XL2002, WinXP "inta251 via OfficeKB.com" wrote: Sorry guys. Please accept my apology. Im not PRO, that why all this confuse. I was thinking from one formula Ill adjust for all columns which need it. Now I see that is not so easy. Now you know I need create average by days. Sheet1 = trips Column A = Days From Columns F to U $$$ amount, some cells will be empty. Sheet2 = average Column A = Days Formula for each cell in Column A {=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$1200, trips!$A$2:$A$1200)0)),SMALL(IF(ISNUMBER(trips!$A $2:$A$1201), IF(ROW(trips!$A$2:$A$1201)=MATCH(trips!$A$2:$A$120 1, trips!$A$2:$A$1201,0)+1,trips!$A$2:$A$1201)),ROW()-1),"")} which generating one day from list of same days. By the way this formula better than I had before. Column B row 2 need formula which will be calculate average from Sheet1 = trips Column F and so on. Sheet1 Sheet2 Col F -------------Col B Col G----------- Col C And so on, till Col U--------------Col Q Need result as posted above MyDate Average 01/08/07 #DIV/0! 01/09/07 #DIV/0! 01/10/07 #DIV/0! 01/11/07 $77.50 01/12/07 $77.50 01/14/07 $77.50 01/15/07 $52.50 01/16/07 $52.50 01/17/07 $76.25 01/18/07 $76.25 01/19/07 $76.25 01/21/07 $76.25 blank $76.25 blank $76.25 blank $76.25 And so on. Once again, THANKS for your time. Sincerely, Igor (inta251) Currently i have formula which giving me correct result {=AVERAGE(IF((trips!$A$2:$A$1200=DATE(2007,1,1))* (trips!$A$2:$A$1200<=$A2), IF(trips!$K$2:$K$1200=0,"",trips!$K$2:$K$1200)))} If possible to add something to clean error #DIV/0! And $76.25 if no new date -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Formula for each cell in Column A
{=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$1200, trips!$A$2:$A$1200)0)),SMALL(IF(ISNUMBER(trips!$A $2:$A$1201), IF(ROW(trips!$A$2:$A$1201)=MATCH(trips!$A$2:$A$120 1, trips!$A$2:$A$1201,0)+1,trips!$A$2:$A$1201)),ROW()-1),"")} which generating one day from list of same days. By the way this formula better than I had before. Actually, the formula you had wasn't too bad calculation-wise. It was just real long! You'll find that these formulas calculate faster than the one above: A2: =MIN(Trips!A2:A1200) A3 copied down: =IF(ROWS($1:2)<=SUM(--(FREQUENCY(Trips!A$2:A$1200,Trips!A$2:A$1200)0)), MIN(IF(Trips!A$2:A$1200A2,Trips!A$2:A$1200)),"") As far as the average formula, since you seem to have a rather large grid of average formulas you would want to avoid array formulas if possible. Try Ron's latest suggestion. Just change the references from Trips column B to Trips column F and use relative addressing for the column. Then you should be able to copy across then down. Biff "inta251 via OfficeKB.com" <u30987@uwe wrote in message news:6daf13c7f08f4@uwe... Sorry guys. Please accept my apology. I'm not PRO, that why all this confuse. I was thinking from one formula I'll adjust for all columns which need it. Now I see that is not so easy. Now you know I need create average by days. Sheet1 = trips Column A = Days From Columns F to U $$$ amount, some cells will be empty. Sheet2 = average Column A = Days Formula for each cell in Column A {=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$1200, trips!$A$2:$A$1200)0)),SMALL(IF(ISNUMBER(trips!$A $2:$A$1201), IF(ROW(trips!$A$2:$A$1201)=MATCH(trips!$A$2:$A$120 1, trips!$A$2:$A$1201,0)+1,trips!$A$2:$A$1201)),ROW()-1),"")} which generating one day from list of same days. By the way this formula better than I had before. Column B row 2 need formula which will be calculate average from Sheet1 = trips Column F and so on. Sheet1 Sheet2 Col F -------------Col B Col G----------- Col C And so on, till Col U--------------Col Q Need result as posted above MyDate Average 01/08/07 blank (because no data available) 01/09/07 blank (because no data available) 01/10/07 blank (because no data available) 01/11/07 $77.50 01/12/07 $77.50 01/14/07 $77.50 01/15/07 $52.50 01/16/07 $52.50 01/17/07 $76.25 01/18/07 $76.25 01/19/07 $76.25 01/21/07 $76.25 And so on. Once again, THANKS for your time. Sincerely, Igor (inta251) Why didn't you tell us that to begin with? Tell us *exactly* where your data is. Where *exactly* are the dates and where *exactly* are the dollar amounts? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
With several hours of testing your formula I found calculation error.
Data from sheet 1 €˜trips. Col A Col K Mon, Jan 08, 2007 Mon, Jan 08, 2007 Mon, Jan 08, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Fri, Jan 12, 2007 Fri, Jan 12, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Mon, Jan 15, 2007 $27.50 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Wed, Jan 17, 2007 Wed, Jan 17, 2007 $123.75 Wed, Jan 17, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Fri, Jan 19, 2007 Fri, Jan 19, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Mon, Jan 22, 2007 Mon, Jan 22, 2007 Mon, Jan 22, 2007 Mon, Jan 22, 2007 Tue, Jan 23, 2007 Tue, Jan 23, 2007 $55.00 Wed, Jan 24, 2007 Wed, Jan 24, 2007 Wed, Jan 24, 2007 Thu, Jan 25, 2007 Thu, Jan 25, 2007 Thu, Jan 25, 2007 Fri, Jan 26, 2007 Fri, Jan 26, 2007 Fri, Jan 26, 2007 Sun, Jan 28, 2007 Sun, Jan 28, 2007 Sun, Jan 28, 2007 Mon, Jan 29, 2007 Mon, Jan 29, 2007 Mon, Jan 29, 2007 Mon, Jan 29, 2007 Mon, Jan 29, 2007 Tue, Jan 30, 2007 Tue, Jan 30, 2007 Tue, Jan 30, 2007 $27.50 Tue, Jan 30, 2007 Wed, Jan 31, 2007 $27.50 Wed, Jan 31, 2007 Wed, Jan 31, 2007 Data from sheet 2 €˜average Col A My formula Your formula Mon, Jan 08, 2007 #DIV/0! Tue, Jan 09, 2007 #DIV/0! Wed, Jan 10, 2007 #DIV/0! Thu, Jan 11, 2007 #DIV/0! Fri, Jan 12, 2007 #DIV/0! Sun, Jan 14, 2007 #DIV/0! Mon, Jan 15, 2007 $27.50 $27.50 Tue, Jan 16, 2007 $27.50 Wed, Jan 17, 2007 $75.63 $75.63 Thu, Jan 18, 2007 $75.63 Fri, Jan 19, 2007 $75.63 Sun, Jan 21, 2007 $75.63 Mon, Jan 22, 2007 $75.63 Tue, Jan 23, 2007 $68.75 $68.75 Wed, Jan 24, 2007 $68.75 Thu, Jan 25, 2007 $68.75 Fri, Jan 26, 2007 $68.75 Sun, Jan 28, 2007 $68.75 Mon, Jan 29, 2007 $68.75 Tue, Jan 30, 2007 $58.44 $52.25 error Wed, Jan 31, 2007 $52.25 $48.13 error $48.13 $48.13 $48.13 $48.13 With my formula calculation correct. Only two problems as I mention above. With your formula =IF(OR(AND($A2<"",ISNUMBER($B1)), SUMIF(trips!$A$2:$A$1201,$A2,trips!$K$2:$K$1201)), AVERAGE(trips!$K$2:INDEX(trips!$K:$K,MATCH(A2+1,tr ips!$A$2:$A$1201,1)-1)),"") On January 30 error occur. Also, I found error in row2. If cell in row2 got some amount all calculation is wrong. Thanks for your time. Sincerely, Igor (inta251) PS: Your and my calculation I checked manually. Ron Coderre wrote: Try this regular formula: =IF(OR(AND($A2<"",ISNUMBER($B1)),SUMIF(Trips!$A$ 2:$A$1201,$A2,Trips!$B$2:$B$1201)),AVERAGE(Trips!$ B$2:INDEX(Trips!$B:$B,MATCH(A2+1,Trips!$A$2:$A$120 1,1)-1)),"") Does that help? *********** Regards, Ron -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
OK....Here's the latest in a series of final solutions <vbg
On the Average sheet Put this regular formula in A2: =IF(MAX(trips!A$2:A$1200)MAX($A$1:A1),MIN(INDEX(( trips!A$2:A$1200N(A1))*(trips!A$2:A$1200)+(trips! A$2:A$1200<=N(A1))*10^9,0)),"") Copy that formula down as far as you need Put this regular formula in B2: =IF(OR(AND($A2<"",ISNUMBER($B1)),SUMIF(trips!$A$2 :$A$1201,$A2,trips!$K$2:$K$1201)),AVERAGE(trips!$K $2:INDEX(trips!$K:$K,COUNTIF(trips!$A$2:$A$1201,"< ="&($A2))+1)),"") Copy that formula down as far as you need Those formula return this list.... MyDate Average 01/08/07 (blank) 01/09/07 (blank) 01/10/07 (blank) 01/11/07 (blank) 01/12/07 (blank) 01/14/07 (blank) 01/15/07 27.50 01/16/07 27.50 01/17/07 75.63 01/18/07 75.63 01/19/07 75.63 01/21/07 75.63 01/22/07 75.63 01/23/07 68.75 01/24/07 68.75 01/25/07 68.75 01/26/07 68.75 01/28/07 68.75 01/29/07 68.75 01/30/07 58.44 01/31/07 52.25 Are we done yet?? *********** Regards, Ron XL2002, WinXP "inta251 via OfficeKB.com" wrote: With several hours of testing your formula I found calculation error. Data from sheet 1 €˜trips. Col A Col K Mon, Jan 08, 2007 Mon, Jan 08, 2007 Mon, Jan 08, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Tue, Jan 09, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Wed, Jan 10, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Thu, Jan 11, 2007 Fri, Jan 12, 2007 Fri, Jan 12, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Sun, Jan 14, 2007 Mon, Jan 15, 2007 $27.50 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Mon, Jan 15, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Tue, Jan 16, 2007 Wed, Jan 17, 2007 Wed, Jan 17, 2007 $123.75 Wed, Jan 17, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Thu, Jan 18, 2007 Fri, Jan 19, 2007 Fri, Jan 19, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Sun, Jan 21, 2007 Mon, Jan 22, 2007 Mon, Jan 22, 2007 Mon, Jan 22, 2007 Mon, Jan 22, 2007 Tue, Jan 23, 2007 Tue, Jan 23, 2007 $55.00 Wed, Jan 24, 2007 Wed, Jan 24, 2007 Wed, Jan 24, 2007 Thu, Jan 25, 2007 Thu, Jan 25, 2007 Thu, Jan 25, 2007 Fri, Jan 26, 2007 Fri, Jan 26, 2007 Fri, Jan 26, 2007 Sun, Jan 28, 2007 Sun, Jan 28, 2007 Sun, Jan 28, 2007 Mon, Jan 29, 2007 Mon, Jan 29, 2007 Mon, Jan 29, 2007 Mon, Jan 29, 2007 Mon, Jan 29, 2007 Tue, Jan 30, 2007 Tue, Jan 30, 2007 Tue, Jan 30, 2007 $27.50 Tue, Jan 30, 2007 Wed, Jan 31, 2007 $27.50 Wed, Jan 31, 2007 Wed, Jan 31, 2007 Data from sheet 2 €˜average Col A My formula Your formula Mon, Jan 08, 2007 #DIV/0! Tue, Jan 09, 2007 #DIV/0! Wed, Jan 10, 2007 #DIV/0! Thu, Jan 11, 2007 #DIV/0! Fri, Jan 12, 2007 #DIV/0! Sun, Jan 14, 2007 #DIV/0! Mon, Jan 15, 2007 $27.50 $27.50 Tue, Jan 16, 2007 $27.50 Wed, Jan 17, 2007 $75.63 $75.63 Thu, Jan 18, 2007 $75.63 Fri, Jan 19, 2007 $75.63 Sun, Jan 21, 2007 $75.63 Mon, Jan 22, 2007 $75.63 Tue, Jan 23, 2007 $68.75 $68.75 Wed, Jan 24, 2007 $68.75 Thu, Jan 25, 2007 $68.75 Fri, Jan 26, 2007 $68.75 Sun, Jan 28, 2007 $68.75 Mon, Jan 29, 2007 $68.75 Tue, Jan 30, 2007 $58.44 $52.25 error Wed, Jan 31, 2007 $52.25 $48.13 error $48.13 $48.13 $48.13 $48.13 With my formula calculation correct. Only two problems as I mention above. With your formula =IF(OR(AND($A2<"",ISNUMBER($B1)), SUMIF(trips!$A$2:$A$1201,$A2,trips!$K$2:$K$1201)), AVERAGE(trips!$K$2:INDEX(trips!$K:$K,MATCH(A2+1,tr ips!$A$2:$A$1201,1)-1)),"") On January 30 error occur. Also, I found error in row2. If cell in row2 got some amount all calculation is wrong. Thanks for your time. Sincerely, Igor (inta251) PS: Your and my calculation I checked manually. Ron Coderre wrote: Try this regular formula: =IF(OR(AND($A2<"",ISNUMBER($B1)),SUMIF(Trips!$A$ 2:$A$1201,$A2,Trips!$B$2:$B$1201)),AVERAGE(Trips!$ B$2:INDEX(Trips!$B:$B,MATCH(A2+1,Trips!$A$2:$A$120 1,1)-1)),"") Does that help? *********** Regards, Ron -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
BINGO!!!!
Thanks to everyone! Special THANKS to Ron! Easy to understand and flexible formula. Thanks to T.Valko. Your idea good as well, but lot off work need to be done. Once again, THANKS to all of YOU. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
BINGO!!!!
Thanks to everyone! Special THANKS to Ron! Easy to understand and flexible formula. Thanks to T.Valko. Your idea good as well, but lot off work need to be done. Once again, THANKS to all of YOU. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Thank you so much for letting us know that worked for you, Igor. I'm glad I
could help. *********** Regards, Ron XL2002, WinXP "inta251 via OfficeKB.com" wrote: BINGO!!!! Thanks to everyone! Special THANKS to Ron! Easy to understand and flexible formula. Thanks to T.Valko. Your idea good as well, but lot off work need to be done. Once again, THANKS to all of YOU. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Hi, Ron!
This formula wich you create for date working perfect. {=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$100,trips!$A$2:$A$100)0) ) ,SMALL(IF(ISNUMBER(trips!$A$2:$A$101),IF(ROW(trips !$A$2:$A$101)=MATCH(trips! $A$2:$A$101,trips!$A$2:$A$101,0)+1,trips!$A$2:$A$1 01)),ROW()-1),"")} Now i have similar situation, but i need formula which working with text. In worksheet 'trips' in column W i put Company names. Same company names infinity. Sample: column W row2 Company1 row3 Company3 row4 Company5 row5 Company1 row6 Company1 row7 Company4 row8 Company5 and so on. Need formula in worksheet 'company' column A row 2 Sample: column A row2 Company1 row3 Company3 row4 Company4 row5 Company5 row6 blank till new company name and so on. THANKS in advence for your time. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Try this:
On the "Company" sheet Put this ARRAY FORMULA (use 'ctrl+shift+enter'....instead of 'enter') in A2: =IF(SUM((trips!$W$2:$W$10000<"")*ISERROR(MATCH(tr ips!$W$2:$W$10000,$A$1:A1,0)))<0,INDEX(trips!$W$2 :$W$10000,MATCH(1,--ISERROR(IF(ISBLANK(trips!$W$2:$W$10000),0,MATCH(tr ips!$W$2:$W$10000,$A$1:$A1,0))),0),1),"") Copy that formula down as far as you need. (adjust range references to suit your situation) Does that help? *********** Regards, Ron XL2002, WinXP "inta251 via OfficeKB.com" wrote: Hi, Ron! This formula wich you create for date working perfect. {=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$100,trips!$A$2:$A$100)0) ) ,SMALL(IF(ISNUMBER(trips!$A$2:$A$101),IF(ROW(trips !$A$2:$A$101)=MATCH(trips! $A$2:$A$101,trips!$A$2:$A$101,0)+1,trips!$A$2:$A$1 01)),ROW()-1),"")} Now i have similar situation, but i need formula which working with text. In worksheet 'trips' in column W i put Company names. Same company names infinity. Sample: column W row2 Company1 row3 Company3 row4 Company5 row5 Company1 row6 Company1 row7 Company4 row8 Company5 and so on. Need formula in worksheet 'company' column A row 2 Sample: column A row2 Company1 row3 Company3 row4 Company4 row5 Company5 row6 blank till new company name and so on. THANKS in advence for your time. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Ron, thanks for respond!
BINGO! BINGO! BINGO! THANKS! THANKS! THANKS! Only one little problem, i can not do sort ascending. But i can live with that. Once again, THANKS! =IF(SUM((trips!$W$2:$W$10000<"")*ISERROR(MATCH(t rips!$W$2:$W$10000,$A$1:A1,0)))<0,INDEX(trips!$W$ 2:$W$10000,MATCH(1,--ISERROR(IF(ISBLANK(trips!$W$2:$W$10000),0,MATCH(tr ips!$W$2:$W$10000,$A$1:$A1,0))),0),1),"") -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Please try this:
With my previously posted formula starting in A2 and copied down Put this formula in B2 and copy down =T(LOOKUP(MATCH(SMALL(INDEX(COUNTIF($A$1:$A$10,"<" &$A$1:$A$10)+($A$1:$A$10="")*10^10,0),ROWS($A$2:A2 )),INDEX(COUNTIF($A$1:$A$10,"<"&$A$1:$A$10)+($A$1: $A$10="")*10^10,0),0),ROW($A$1:$A$10),$A$1:$A$10)) That formula will list the Col_A values in ascending order. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "inta251 via OfficeKB.com" wrote: Ron, thanks for respond! BINGO! BINGO! BINGO! THANKS! THANKS! THANKS! Only one little problem, i can not do sort ascending. But i can live with that. Once again, THANKS! =IF(SUM((trips!$W$2:$W$10000<"")*ISERROR(MATCH(t rips!$W$2:$W$10000,$A$1:A1,0)))<0,INDEX(trips!$W$ 2:$W$10000,MATCH(1,--ISERROR(IF(ISBLANK(trips!$W$2:$W$10000),0,MATCH(tr ips!$W$2:$W$10000,$A$1:$A1,0))),0),1),"") -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
This is just perfectoooooo!!!!!
I did just as you posted. Working 100%+. To make nice view, i just hide column A. Sometime i am wondering, how you guys can keep in the head all this formulas. Anyway, THANKS for your help. Sincerely, Igor. (inta251) Put this formula in B2 and copy down =T(LOOKUP(MATCH(SMALL(INDEX(COUNTIF($A$1:$A$10,"< "&$A$1:$A$10)+($A$1:$A$10="")*10^10,0),ROWS($A$2:A 2)),INDEX(COUNTIF($A$1:$A$10,"<"&$A$1:$A$10)+($A$1 :$A$10="")*10^10,0),0),ROW($A$1:$A$10),$A$1:$A$10) ) -- Message posted via http://www.officekb.com |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average by Date
Thanks for letting me know that worked for you.....I'm glad I could help.
*********** Regards, Ron XL2002, WinXP "inta251 via OfficeKB.com" wrote: This is just perfectoooooo!!!!! I did just as you posted. Working 100%+. To make nice view, i just hide column A. Sometime i am wondering, how you guys can keep in the head all this formulas. Anyway, THANKS for your help. Sincerely, Igor. (inta251) Put this formula in B2 and copy down =T(LOOKUP(MATCH(SMALL(INDEX(COUNTIF($A$1:$A$10,"< "&$A$1:$A$10)+($A$1:$A$10="")*10^10,0),ROWS($A$2:A 2)),INDEX(COUNTIF($A$1:$A$10,"<"&$A$1:$A$10)+($A$1 :$A$10="")*10^10,0),0),ROW($A$1:$A$10),$A$1:$A$10) ) -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count between start date and end date | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Calculate difference between 2 date and times with average | Excel Discussion (Misc queries) | |||
Another Date issue. | Excel Worksheet Functions | |||
Average to date | Excel Worksheet Functions |