Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

average fields depending on criteria 1

Status
Not open for further replies.
Epsilon101

Epsilon101

Programmer
Mar 30, 2004
384
GB
Hi, this is example data in a separate sheet:

Owner Reference OpenDate ClosedDate
39007 12345543442 14/04/2004 15/05/2004
39007 36363453544 30/09/2004 ?
39007 86565545453 02/04/2004 07/05/2004
39004 56780957463 07/06/2004 23/06/2004
39004 87486765489 25/05/2004 ?
39005 23456789053 22/04/2004 21/06/2004
39005 34343439409 04/05/2004 19/07/2004


I used =IF(ISERROR(D2-C2),"",(D2-C2)) to give a day count from open to closed and blank cell if not complete.

This formula is a field called RANGE, i want to average the values in this field depending on the owner code.

So in another worksheet i have 39007, that owner has 3 references, 2 of these are complete, the values for these in the RANGE field are 31 & 35 respectively, so average will come out as 33 days.

I am not sure how i could do this, im also doing it over 2 worksheets, formulae in 1, data in the other.

---------------------------------------

Neil
 
Sort by date Sort by votes
I would copy your days-elapsed formula to Sheet1!E2:E7 so that the days elapsed come out next to the respective reference.

Then, you can use IF, ISERROR, and AVERAGE to output the average wherever you want it: =AVERAGE(IF(ISERROR(Sheet1!E2:E7), "", Sheet1!E2:E7))

This should filter out the error values.

Good luck,
Eli

Eli Morris-Heft
"If at first you don't succeed, skydiving is not for you.
 
Upvote 0 Downvote
Hi Epsilon101,

If your sample data is in rows 2 through 8 ..

[blue][tt] =AVERAGE(IF(A$2:A$8=A2,IF(ISERROR(D$2:D$8-C$2:C$8),"",D$2:D$8-C$2:C$8)))[/tt][/blue]

.. array entered in E2 and copied down will give you the averages you are looking for without direct reference to your other formula. I'm sure you can adjust to include sheet references, and change the A2 to point to wherever you have the owner ID you want to check against.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Thanks for the reply Eli,

The formula you have mentioned would work if i was doing E2:E7 but what i want to do is Average the days elapsed depending on the owner in column A, my data is about 3000 rows so i have been doing E:E.

The formula i am using to calculate the days elapsed is in column E, this sheet is called 'data', i have a 'summary' sheet being used to display all information,
which is where i want to display the average.




---------------------------------------

Neil
 
Upvote 0 Downvote
Sorry the sample was a just a small sample of the data i have, i tried your formula Tony, i put the below in the 'summary' sheet.

=AVERAGE(IF(Data!A$1:A$5000=A3,IF(ISERROR(Data!F$1:F$5000-Data!E$1:E$5000),"",Data!F$1:F$5000-Data!E$1:E$5000)))

it didnt error, just not coming back with any averages.

I have just tried something like this

Cell Summary!A1 = =SUMPRODUCT((Data!A1:A5000=A5)*(Data!E1:E5000<>"")*(Data!E1:E5000))

Cell Summary!A2 = =SUMPRODUCT((Data!A1:A5000=A5)*(Data!E1:E5000<>""))

then do Summary!A1/Summary!A2 to get the average.

The formula in A1 returns a #VALUE error, most likely cos i am referencing Column E twice... Wondering what to do. Any thoughts??

---------------------------------------

Neil
 
Upvote 0 Downvote
sorry =AVERAGE(IF(Data!A$1:A$5000=A3,IF(ISERROR(Data!F$1:F$5000-Data!E$1:E$5000),"",Data!F$1:F$5000-Data!E$1:E$5000)))

is
=AVERAGE(IF(Data!A$1:A$5000=A3,IF(ISERROR(Data!E$1:E$5000-Data!D$1:D$5000),"",Data!E$1:E$5000-Data!D$1:D$5000)))

Just the sheet i am working in has extra irrelevant columns i havent included in the sample, dont want to confuse anyone

---------------------------------------

Neil
 
Upvote 0 Downvote
Hi Neil,

Formula looks fine.
Did you ARRAY-ENTER it?
Press Ctrl+Shift+Enter (instead of just Enter) to apply it - it will appear in the cell wrapped in braces.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Ill give that a go

---------------------------------------

Neil
 
Upvote 0 Downvote
Yea works, gives a #div/o! error if the owner doesnt have any records in the data, which is right, just wondering how to change it from #div/o! to 0

i tried

=AVERAGE(IF(ISERROR(Data!A$1:A$5000=A3)),0,IF(ISERROR(Data!E$1:E$5000-Data!D$1:D$5000),"",Data!E$1:E$5000-Data!D$1:D$5000)))

but then it came back with 6 and this owner has no references at the moment, so thats not right lol.

Any chance you could explain this formula to me because i am having trouble getting how it works, i get IF statements and i get Average but putting them together this way is throwing me a bit.


---------------------------------------

Neil
 
Upvote 0 Downvote
Hi Neil,

Ignoring the Div/0 error on the average at the moment, the formula is:

[blue][tt]=AVERAGE([/tt][/blue][red][tt]IF([/tt][/red][green][tt]Data!A$1:A$5000=A3[/tt][/green],[purple][tt]IF(ISERROR(Data!E$1:E$5000-Data!D$1:D$5000),"",Data!E$1:E$5000-Data!D$1:D$5000)[/tt][/purple][red][tt])[/tt][/red][blue][tt])[/tt][/blue]

Also ignore the Error on the 'E - D' section for the moment and we get:

[blue][tt]=AVERAGE([/tt][/blue][red][tt]IF([/tt][/red][green][tt]Data!A$1:A$5000=A3[/tt][/green],[purple][tt]Data!E$1:E$5000-Data!D$1:D$5000[/tt][/purple][red][tt])[/tt][/red][blue][tt])[/tt][/blue]

It's an array formula and works with arrays. The [red][tt]IF(...)[/tt][/red] produces an 5000-strong array of

[red][tt]IF([/tt][/red][green][tt]Data!A$1=A3[/tt][/green],[purple][tt]Data!E$1-Data!D$1[/tt][/purple][red][tt])[/tt][/red]
[red][tt]IF([/tt][/red][green][tt]Data!A$2=A3[/tt][/green],[purple][tt]Data!E$2-Data!D$2[/tt][/purple][red][tt])[/tt][/red]
[red][tt]IF([/tt][/red][green][tt]Data!A$3=A3[/tt][/green],[purple][tt]Data!E$3-Data!D$3[/tt][/purple][red][tt])[/tt][/red]
:
:
[red][tt]IF([/tt][/red][green][tt]Data!A$4999=A3[/tt][/green],[purple][tt]Data!E$4999-Data!D$4999[/tt][/purple][red][tt])[/tt][/red]
[red][tt]IF([/tt][/red][green][tt]Data!A$5000=A3[/tt][/green],[purple][tt]Data!E$5000-Data!D$5000[/tt][/purple][red][tt])[/tt][/red]

When the condition is True, in other words the owner in, say, Data1!A1 is equal to the one in A3 (on the sheet the formula is in) then the array element is E1-D1 (from the Data Sheet). Now this sometimes errors so we apply the IF(ISERROR( to each element so that the actual array elements created are either valid or empty.

Where the condition is False, the array element is FALSE.

The resulting array is then averaged. The FALSE values are ignored by the function, as are the empty strings.

Now the DIV/0 error for the whole thing. I did knock up a sheet to test originally but didn't keep it so this is untested. The Average function is the one producing the error so it is (the result of) the average function which needs testing and the whole thing would need repeating, like this ..

[red][tt]=IF(ISERROR([/tt][/red][green][tt]AVERAGE(IF(Data!A$1:A$5000=A3,IF(ISERROR(Data!E$1:E$5000-Data!D$1:D$5000),"",Data!E$1:E$5000-Data!D$1:D$5000)))[/tt][/green][red][tt]),"",[/tt][/red][blue][tt]AVERAGE(IF(Data!A$1:A$5000=A3,IF(ISERROR(Data!E$1:E$5000-Data!D$1:D$5000),"",Data!E$1:E$5000-Data!D$1:D$5000)))[/tt][/blue][red][tt])[/tt][/red]

It gets a bit convoluted. A better way might be to check the source data - assuming your question marks are actually empty cells ..

[red][tt]=IF([/tt][/red][green][tt]COUNTA(Data1!E$1:E$5000)=0[/tt][/green][red][tt]),"",[/tt][/red][blue][tt]AVERAGE(IF(Data!A$1:A$5000=A3,IF(ISERROR(Data!E$1:E$5000-Data!D$1:D$5000),"",Data!E$1:E$5000-Data!D$1:D$5000)))[/tt][/blue][red][tt])[/tt][/red]

I've tried to colour parts of the formulae to make it clearer - sorry if I've made any mistakes - I've done it here and not in Excel, so I hope it's all clear; if not, do come back.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Thanks Tony great explanation, it was the iserror ignoring parts of a range when an error occurs instead of the whole thing that was getting me.

One last thing, can i ask what the Ctrl+shift+enter is all about??

---------------------------------------

Neil
 
Upvote 0 Downvote
Hi Neil,

Ctrl+Shift+Enter is just the way to enter a formula as an array formula, to make a formula which normally works on single values work on arrays of them.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Ah get it now Gah.

My ? are ?'s the report comes back with these when a reference hasnt reached the close date, i tried
Code: 
=IF(ISERROR(AVERAGE(IF(Data!A$1:A$5000=A3,IF(ISERROR(Data!E$1:E$5000-Data!D$1:D$5000),"",Data!E$1:E$5000-Data!D$1:D$5000)))),"",AVERAGE(IF(Data!A$1:A$5000=A3,IF(ISERROR(Data!E$1:E$5000-Data!D$1:D$5000),"",Data!E$1:E$5000-Data!D$1:D$5000)))) & " Days"
it looks fine to me, but when i try it, its not letting me ctrl+shift+enter so all the values are coming back as 0

---------------------------------------

Neil
 
Upvote 0 Downvote
Hi Neil,

I just cut and pasted into Excel and it works for me. Do you get an error message? Does it highlight anything or position the cursor anywhere within the formula when it fails?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
I think its the keys used for changing it to an array, ctrl+shift+enter isnt doing anything??? is there a way to do it without hotkeys?

---------------------------------------

Neil
 
Upvote 0 Downvote
Oh sorry it does work, lol just me doing it wrong. I was selecting the cell and doing Ctrl+Shift+Enter when yesterday i did it while the formula bar was selected, just forgot thats the way to do it.

It works great now, thanks a lot for all your help Tony.

---------------------------------------

Neil
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Excel Match / Index on multiple values and criteria
Replies
6
Views
438
remeng
remeng
MM1963
  • Locked
  • Question
Excel - Unique count using a criteria 2
Replies
19
Views
803
SkipVought
S
arvarr
  • Locked
  • Question
Pivot Table - Calculated Fields / Different From 1
Replies
12
Views
577
combo
combo
navyasree
  • Locked
  • Question
Need to update excel on making changes in visio flowcharts 1
Replies
4
Views
555
Andrzejek
Andrzejek
ladyck3
  • Locked
  • Question
Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2 6
Replies
14
Views
740
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top