Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2012, 04:21 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default 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


Reply With Quote
  #2  
Old 08-08-2012, 04:26 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline formula to work from data column Windows 7 32bit formula to work from data column Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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?
Reply With Quote
  #3  
Old 08-08-2012, 04:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline formula to work from data column Windows 7 64bit formula to work from data column Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,507
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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
Reply With Quote
  #4  
Old 08-08-2012, 05:29 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

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
File Type: xlsx example.xlsx (9.2 KB, 5 views)
Reply With Quote
  #5  
Old 08-08-2012, 06:18 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline formula to work from data column Windows 7 32bit formula to work from data column Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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")
Reply With Quote
  #6  
Old 08-08-2012, 06:24 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

thank you very much.

excellent stuff
Reply With Quote
  #7  
Old 08-08-2012, 06:46 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 08-08-2012, 06:50 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

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!
Reply With Quote
  #9  
Old 08-08-2012, 07:05 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline formula to work from data column Windows 7 32bit formula to work from data column Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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()))
Reply With Quote
  #10  
Old 08-08-2012, 07:10 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

they are dates
Reply With Quote
  #11  
Old 08-08-2012, 07:19 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

is there a way i can get round the #DIV/0!
Reply With Quote
  #12  
Old 08-08-2012, 07:20 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline formula to work from data column Windows 7 32bit formula to work from data column Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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)
Reply With Quote
  #13  
Old 08-08-2012, 07:27 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

still getting #DIV/0!
Reply With Quote
  #14  
Old 08-08-2012, 08:13 AM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

its ok, ive worked it out.

thank you again for you help.

Regards

Rob
Reply With Quote
  #15  
Old 08-08-2012, 02:35 PM
masterdineen masterdineen is offline formula to work from data column Windows XP formula to work from data column Office 2010 32bit
Novice
formula to work from data column
 
Join Date: Aug 2012
Posts: 11
masterdineen is on a distinguished road
Default

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?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change Resource Time vs. Duration when Updating Work column lylel Project 3 07-23-2012 06:09 PM
Displaying Work Week instead of date in a column. whitney07 Project 1 07-05-2012 02:27 PM
formula to work from data column definition of work column ketanco Project 9 05-31-2012 05:15 PM
formula to work from data column Right-Aligning Data to a Column sunnyside Excel 3 02-27-2012 01:25 AM
Formula not getting copied to entire column 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.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft