Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-28-2014, 09:00 AM
bdouglas1011 bdouglas1011 is offline Looking for formula that would create an average from certain criteria with in excel Windows 7 64bit Looking for formula that would create an average from certain criteria with in excel Office 2013
Novice
Looking for formula that would create an average from certain criteria with in excel
 
Join Date: Jul 2014
Posts: 5
bdouglas1011 is on a distinguished road
Default Looking for formula that would create an average from certain criteria with in excel

I have attached (2) sheets - (Book1) is what I have working & would like to know if you think there is any easier way to do the same thing and receive the same output. (Book2) is the way it comes whit out any changes on my part.

As you see on Book1 - The blue color indicates anything happening between the hours of 1800 & 0600.
The Grey are things happening between 1800 & 0600.

Normally there would be no highlighting on this sheet I did that to breakdown activity that happened between those times of the day

As of now I insert a module that looks for 2 user defined functions.

What I want is a breakdown of the Average of the (Drilling & Sliding) You can see the output i Have already and it works good.


I am looking for the Average ROP for drilling & sliding that occurred B/W the hours of 0600 & 1800 and then the Average of Sliding that occurred during the same times.

Then I am looking for the same thing B/W the hours of 1800 & 0600:

We used military times to know whether it is daytime or night time.
I am trying to be able to get away of highlighting the activity but still come out with the same answers.

Book2 is the way it comes before I change anything - Any help would be great or is the way I do it the easiest way to get these results.
Attached Files
File Type: xlsm Book1.xlsm (26.2 KB, 7 views)
File Type: xlsx Book2.xlsx (16.4 KB, 7 views)
Reply With Quote
  #2  
Old 07-28-2014, 12:23 PM
whatsup whatsup is offline Looking for formula that would create an average from certain criteria with in excel Windows 7 64bit Looking for formula that would create an average from certain criteria with in excel Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Hi

Without coloring cells, just based on formulas to calculate the average regarding certain criteria.
You can include empty cells in the selceted range for calculation, it won't affect the result, but I recomend generally to keep ranges as tight as possible.
Attached Files
File Type: xlsx AVERAGE(IF(_.xlsx (18.0 KB, 15 views)
Reply With Quote
  #3  
Old 07-28-2014, 12:27 PM
bdouglas1011 bdouglas1011 is offline Looking for formula that would create an average from certain criteria with in excel Windows 7 64bit Looking for formula that would create an average from certain criteria with in excel Office 2013
Novice
Looking for formula that would create an average from certain criteria with in excel
 
Join Date: Jul 2014
Posts: 5
bdouglas1011 is on a distinguished road
Default

I was trying to open your attachment but it wont open something with PHP
Reply With Quote
  #4  
Old 07-28-2014, 12:55 PM
whatsup whatsup is offline Looking for formula that would create an average from certain criteria with in excel Windows 7 64bit Looking for formula that would create an average from certain criteria with in excel Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Yes, that's a bug the forum comes with

If you got IE in use, click the desired file. You will get options what to do:
Choose "save as" and type in the name of the file including its format (.xlsx), select the folder where the file should be saved and click ok.
From the folder you can open the file normally.
Reply With Quote
  #5  
Old 07-29-2014, 07:30 AM
bdouglas1011 bdouglas1011 is offline Looking for formula that would create an average from certain criteria with in excel Windows 7 64bit Looking for formula that would create an average from certain criteria with in excel Office 2013
Novice
Looking for formula that would create an average from certain criteria with in excel
 
Join Date: Jul 2014
Posts: 5
bdouglas1011 is on a distinguished road
Default Expanding the range

Some of the sheets I apply this formulas to has more and some has less rows. Can I not just go in the formula and change the range ($d$2:$d$69) just change the 69 to say 300 and the other corresponding parts to expand for more rows in other sheets. I tried that and now the data returns as Zero or extremely off.
Reply With Quote
  #6  
Old 07-29-2014, 07:46 AM
bdouglas1011 bdouglas1011 is offline Looking for formula that would create an average from certain criteria with in excel Windows 7 64bit Looking for formula that would create an average from certain criteria with in excel Office 2013
Novice
Looking for formula that would create an average from certain criteria with in excel
 
Join Date: Jul 2014
Posts: 5
bdouglas1011 is on a distinguished road
Default

I also notice that sometimes the result changes slightly on same data, I have applied the one you sent to my same sheet and I get the same answer but then if I click with in the formula cell it will change the answer from 309 - 249. I can see no difference in the formula's when I match them mine is the same is yours.
=AVERAGE(IF(((($D$2:$D$69)*1>=W$2)+(($D$2:$D$69)*1 <=W$3))*($C$2:$C$69=X2),$K$2:$K$69))

Why would it do that? I should get the same answer - all the data is the same
Reply With Quote
  #7  
Old 07-29-2014, 07:52 AM
whatsup whatsup is offline Looking for formula that would create an average from certain criteria with in excel Windows 7 64bit Looking for formula that would create an average from certain criteria with in excel Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

As I said - Yes you can, as long additional range is really empty, AVERAGE() ignores these cells. Empty means, that the additional cells may not filled with Zero neither with empty strings (="") as formulas might return.

The formulas used are Array-formulas. They tend to be slow, therefore it's recommended to keep ranges short for performance issues.

If you do any changes remember you will have to quit the changes by pressing Ctrl+Shift+Enter at one time instead of only Enter. With this Excel will then suround the formula by {} to mark the formula as Array-formula. Only that way the formula will return correct results.

Edit:
As to your second question, it's probably the result of above said
Reply With Quote
  #8  
Old 07-29-2014, 08:16 AM
bdouglas1011 bdouglas1011 is offline Looking for formula that would create an average from certain criteria with in excel Windows 7 64bit Looking for formula that would create an average from certain criteria with in excel Office 2013
Novice
Looking for formula that would create an average from certain criteria with in excel
 
Join Date: Jul 2014
Posts: 5
bdouglas1011 is on a distinguished road
Default

Thank you ...I got it using the Array that's what I forgot
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average over all pivot rows in excel Andrey Excel 2 07-23-2014 07:51 AM
formula to find 2 or more criterias then average the sum samtitus Excel 6 02-17-2014 10:16 PM
Looking for formula that would create an average from certain criteria with in excel Formula with 3 Criteria bremen22 Excel 3 09-24-2013 11:39 AM
Looking for formula that would create an average from certain criteria with in excel Excel 2010 formula rounds - arithmetic average calculation YooNaa Kim Excel 1 06-07-2011 08:50 PM
'AVERAGE' Formula nfphilpot Excel 3 11-24-2010 02:19 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:19 AM.


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