Microsoft Office Forums formula to work from data column
 Register FAQ Search Today's Posts Mark Forums Read

#1
08-08-2012, 04:21 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11
formula to work from data column

Hello

I have the following equation =AVERAGEIF(SUPPORT!K9:K99, ">0")

support!K9:K99 being number of daydiff.

I want to select the daydiff values based on another date column, that only has the current month and year. ie Aug(8) & 2012

#2
08-08-2012, 04:26 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

Welcome to the forum. Please would you attach an example worksheet (with no confidential data) and tell us the result you want to help us provide the best solution to your question?
#3
08-08-2012, 04:41 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,507

Perhaps
Code:
`=AVERAGE(IF((SUPPORT!K9:K99, ">0")*(month(j9:j99)=8)*(year(j9:j99)=2012)))`
where col J contains your dates

Enter this array formula with Ctrl+Shift+Enter
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
#4
08-08-2012, 05:29 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

I have attached a exmple of data

I would like to average the score column based on the current month and year

Regards

Rob
Attached Files
 example.xlsx (9.2 KB, 5 views)
#5
08-08-2012, 06:18 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

Hi Rob,

Since you have more than one condition you have to use AVERAGEIFS instead of AVERAGEIF.
Code:
`=AVERAGEIFS(E3:E100,C3:C100,MONTH(TODAY()),D3:D100,YEAR(TODAY()))`
If you also want to exclude scores less than or equal to zero then include a third condition:
Code:
`=AVERAGEIFS(E3:E100,C3:C100,MONTH(TODAY()),D3:D100,YEAR(TODAY()),E3:E100,">0")`
You could simplify (and improve) things further by putting =TODAY() in a spare cell (eg cell H1) and then referecing it from your formula, such as
Code:
`=AVERAGEIFS(E3:E100,C3:C100,MONTH(H1),D3:D100,YEAR(H1),E3:E100,">0")`
#6
08-08-2012, 06:24 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

thank you very much.

excellent stuff
#7
08-08-2012, 06:46 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

would this work when the Month and year figures are made up from formulars as well.

eg the month column is made up like. =month(c3:c400) c3:c400 is full of dates.
#8
08-08-2012, 06:50 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

i am using the following equation

=AVERAGEIFS(SUPPORT!K9:K258,SUPPORT!J9:J258,MONTH( TODAY()),SUPPORT!J9:J258,YEAR(TODAY()))

and i get #DIV/0!
#9
08-08-2012, 07:05 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

You've referenced column J for both the Month and the Year criteria. What does column J contain?
Code:
`=AVERAGEIFS(SUPPORT!K9:K258,SUPPORT!J9:J258,MONTH( TODAY()),SUPPORT!J9:J258,YEAR(TODAY()))`
#10
08-08-2012, 07:10 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

they are dates
#11
08-08-2012, 07:19 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

is there a way i can get round the #DIV/0!
#12
08-08-2012, 07:20 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

Okay, your original example had month and year numbers in separate cells. If you want to reference a single column which contains dates then a different formula is required. Here's one variation:
Code:
```=AVERAGEIFS(SUPPORT!K9:K258,
SUPPORT!J9:J258,">="&EOMONTH(TODAY(),-1)+1,
SUPPORT!J9:J258,"<"&EOMONTH(TODAY(),0)+1)```
#13
08-08-2012, 07:27 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

still getting #DIV/0!
#14
08-08-2012, 08:13 AM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

its ok, ive worked it out.

thank you again for you help.

Regards

Rob
#15
08-08-2012, 02:35 PM
 masterdineen Windows XP Office 2010 32bit Novice Join Date: Aug 2012 Posts: 11

now i am trying the following formula

=COUNTIFS(SUPPORT!H8:H25,"Martin",SUPPORT!M9:M25,Y EAR(TODAY()))

and i get #Value!

but when i create the same formula on the support sheet.

ie =COUNTIFS(H9:H250,"Martin",M9:M250,YEAR(TODAY())) and it works.

Is there limits when referencing other sheets in workbook?

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post lylel Project 3 07-23-2012 06:09 PM whitney07 Project 1 07-05-2012 02:27 PM ketanco Project 9 05-31-2012 05:15 PM sunnyside Excel 3 02-27-2012 01:25 AM theacegary Excel 1 11-29-2010 02:18 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:37 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top