Formula to select cells based on text in other cells

[Guest]

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 (D2366) I need a formula to Average the data in (D2366)
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.

 

[Guest]

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

 

[Guest]

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 array
formula.

HTH
Jon


--
Jon von der Heyden
------------------------------------------------------------------------
Jon von der Heyden's Profile: http://www.officehelp.in/member.php?userid=4852
View this thread: http://www.officehelp.in/showthread.php?t=1267843

Posted from - http://www.officehelp.in

Hi

You could use a helper column I used column F. then enter this formula

=SUMPRODUCT(--(F2:F18>=2)*(F2:F18<7)*(D218)/NETWORKDAYS(C2,C18))

This is not an array formula and can be entered normally. Change the ranges
to suit.

Regards
Peter

 

[Guest]

--
Frustrated 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))
Confirmed with Ctrl+Shift+Enter and not just Enter as this is an array
formula.

HTH
Jon


--
Jon von der Heyden
------------------------------------------------------------------------
Jon von der Heyden's Profile: http://www.officehelp.in/member.php?userid=4852
View this thread: http://www.officehelp.in/showthread.php?t=1267843

Posted from - http://www.officehelp.in

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

 

[Guest]

--
Frustrated user

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 an
average of all entries apart from "Sat" and "Sun"...

Regards,
Jon


--
Jon von der Heyden
------------------------------------------------------------------------
Jon von der Heyden's Profile: http://www.officehelp.in/member.php?userid=4852
View this thread: http://www.officehelp.in/showthread.php?t=1267843

Posted from - http://www.officehelp.in

Wow 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