 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Formula to select cells based on text in other cells
I have a large number of worksheets with electricity readings on them for various sites. there are 48 columns of readings (every 1/2 hour) with 365 days of the year in rows. I need to seperate out the data for the week days and average it out for each half hour column as none of our plants work at a weekend. For instance I have the days in one column (C2:C366) and the half hour data starts in column (D2:D366) I need a formula to Average the data in (D2:D366) based on the day of the week in (C2:C366) ie Monday to Friday but not Sat/Sun. I do not want to delete the data for the weekends as I need to use it else where. Thanks in advance.  SignatureFrustrated user What is the format in C2:C366? Are they dates? If so then: =AVERAGE(IF(WEEKDAY(C2:C366,2)<6,(D2:D366)) confirmed with Ctrl+Shift+Enter If not I recommend you add a new column to create a date for each da and run the same formula... HTH Jo -- Jon von der Heyde Posted from - http://www.officehelp.i John I have columns with date (B) and day(C) it is the days (Saturday and Sunday) I need to exclude from the data, I suppose I could select every 6th and 7th row to exclude but as an infregent user of formulas in Excel I don't know how to do that either. Many thanks SignatureFrustrated user > What is the format in C2:C366? Are they dates? > If so then: [quoted text clipped - 5 lines] > HTH > Jon Ok, then I think the formula should be: =AVERAGE(IF(($C$2:$C$366<>"Saturday")*($C$2:$C$366<>"Sunday"),$D$2:$D$366)) Confirmed with Ctrl+Shift+Enter and not just Enter as this is an arra formula. HTH Jon :- -- Jon von der Heyde Posted from - http://www.officehelp.i > Ok, then I think the formula should be: > =AVERAGE(IF(($C$2:$C$366<>"Saturday")*($C$2:$C$366<>"Sunday"),$D$2:$D$366)) [quoted text clipped - 3 lines] > HTH > Jon :-) Hi You could use a helper column I used column F. then enter this formula =SUMPRODUCT(--(F2:F18>=2)*(F2:F18<7)*(D2:D18)/NETWORKDAYS(C2,C18)) This is not an array formula and can be entered normally. Change the ranges to suit. Regards Peter SignatureFrustrated user > Ok, then I think the formula should be: > =AVERAGE(IF(($C$2:$C$366<>"Saturday")*($C$2:$C$366<>"Sunday"),$D$2:$D$366)) [quoted text clipped - 3 lines] > HTH > Jon :-) I have tried various combinations and still no joy. Probably I have not explained very well what I am trying to do so more explanation below. Date Day Kwh1 Kwh2 Kwh3 Kwh4 >>>>>>Kwh48 2006/01/01 Sun 39.3 36.5 35.2 31.9 >>>>>>>31.5 2006/01/02 Mon 38.5 35.3 35.4 36.6 >>>>>>>32.5 2006/01/03 Tue 41.5 38.7 36.7 33.7 >>>>>>>38.2 " " " " " " " 2006/11/21 Tue 39.7 38.6 35.4 34.9 >>>>>>>37.3 The worksheet is in the format above with 48 columns of data readings with a row for each day for the number of days in the year so above has rows to the 21st of Nov, the full year has 365/6 data rows. I need to easily find the average of each column minus the Sat and Sun readings. I hope this is a better explanation of what I am trying to do and many many thanks for the time you and Peter have spent so far. Phrontis Hi, Have a look here at an example I've made available to you http://www.box.net/public/19gj3iqbik Note the formula in cell A368 - an array formula that formulates a average of all entries apart from "Sat" and "Sun"... Regards, Jo -- Jon von der Heyde Posted from - http://www.officehelp.i SignatureFrustrated user > Hi, > [quoted text clipped - 6 lines] > Regards, > Jon I thought you must have hacked into my PC the spreadsheet looked so right. I can't thank you enough for the amount of time you have spent on this so far, and I was right I still have not explained myself quite right. Your solution averages all of the data but Sat/Sun, I need it to average EACH column. With the average at the bottom of each column I can then add up each pair of 1/2 hour of data to get the total for the hour, I then graph this to get the actual electricty profile for the working week. As a matter of interest during the day the Kwh go upto about 285Kwh on this site. We are looking at putting in a CHP plant to save energy and produce less CO2 in our production processes. This site will not make financial sense I think, but one of our other premises which uses upto 760Kwh may just scrape in. Sorry its taken a day or to to get back but other things have raised their heads. All the best Phrontis |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.