|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
I was trying to open your attachment but it wont open something with PHP
|
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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.
|
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Thank you ...I got it using the Array that's what I forgot
|
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 |
Formula with 3 Criteria | bremen22 | Excel | 3 | 09-24-2013 11:39 AM |
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 |