Calculate Average on specific items
 

I'd like to be able to calculate the average based on a weekday to get a
runrate by day, but I'm having trouble -- below is an example

Weekday Amount
Mon 10
Tues 20
Mon 20
Tues 30

Mon -- I'd like a formula that would return a value of 15(the
(10+20)/2)

Can anyone help???

Thx

Calculate Average on specific items
 

Try:

=SUM((A2:A5="Mon")*(B2:B5))/COUNTIF(A2:A5,"Mon")

Array entered with Ctrl + Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"terri" wrote in message
...
I'd like to be able to calculate the average based on a weekday to get a
runrate by day, but I'm having trouble -- below is an example

Weekday Amount
Mon 10
Tues 20
Mon 20
Tues 30

Mon -- I'd like a formula that would return a value of 15(the
(10+20)/2)

Can anyone help???

Thx

Calculate Average on specific items
 

With your sample data in A1:B5
Weekday Amount
Mon 10
Tues 20
Mon 20
Tues 30

Assuming:
Col_A contains text, not dates formatted to show the day
Col_B blanks or text count as zero

Try this:
D1: (the day to search for.....eg Mon)

This formula returns the average for that day
=SUMIF(A1:A5,D1,B1:B5)/COUNTIF(A1:A5,D1)

Is that something you can work with?

(Post back if you have more questions)
***********
Regards,
Ron

XL2003, WinXP


"terri" wrote:

I'd like to be able to calculate the average based on a weekday to get a
runrate by day, but I'm having trouble -- below is an example

Weekday Amount
Mon 10
Tues 20
Mon 20
Tues 30

Mon -- I'd like a formula that would return a value of 15(the
(10+20)/2)

Can anyone help???

Thx

Calculate Average on specific items
 

"Sandy Mann" wrote...
Try:

=SUM((A2:A5="Mon")*(B2:B5))/COUNTIF(A2:A5,"Mon")
....

Or just

=SUMIF(A2:A5,"Mon",B2:B5)/COUNTIF(A2:A5,"Mon")

without array entry.

Calculate Average on specific items
 

=AVERAGE(IF(A2:A5="Mon",B2:B5))

Enter with Ctrl+Shift+Enter

I am assuming "Mon" is a literal (text)

"terri" wrote:

I'd like to be able to calculate the average based on a weekday to get a
runrate by day, but I'm having trouble -- below is an example

Weekday Amount
Mon 10
Tues 20
Mon 20
Tues 30

Mon -- I'd like a formula that would return a value of 15(the
(10+20)/2)

Can anyone help???

Thx