Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count month-specific items | Excel Discussion (Misc queries) | |||
Query Items Listed During a Specific Time Period | Excel Discussion (Misc queries) | |||
Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
entering items on specific dates of year | Excel Discussion (Misc queries) | |||
How do I copy specific items to another worksheet? | Excel Worksheet Functions |