Small Excell Question

Luminance · February 22, 2011, 07:57:35 AM

Good morning.

I've been busy in excell and I have several huge raw datasheets from our production machine, containing everything from temperatures to speeds or percentages. Now I would like to average the values of every collumn at certain times (depending on the batches). Luckily the date/time has been added (per second) so thats good, however the datalogger has some points where the values drop due too sealed splices and/or is calibrating. At those times the values are significantly lower, so I'm needing a average formula with conditions.
Now I have this formula:
=AVERAGEIFS(C369:C518;C369:C518;">2";C369:C518;"<10")

However it would be a lot easier if i could change the >2 into >P8 or something and put a 2 in cell P8. Now this is where my question starts, is it possible to addept the formula in such a way that something like:
=AVERAGEIFS(C369:C518;C369:C518;>P8;C369:C518;P8" and "is working, as this doesn't seem too work.

If so then this means I can just drag the cell around without needing to change the conditions for every single collumn and time period.

Thanks in advance,
Lumi

Also maybe instaid of C369:C518 you guys know a way to transform the formula in such a way that I can just add in a time, lets say the average of the values between 8-2-2011 8:30 and 8-2-2011 9:15 with the conditions.
(this is not needed but if you guys have experience with it, and know it out of the top of your head then that would save me quite some time)

Benny · February 22, 2011, 08:54:07 AM

hmmmm.....works for me, or am I reading your post wrong?

[ATTACH=CONFIG]638[/ATTACH]

Luminance · February 22, 2011, 09:16:48 AM

thats not exactly what i mean, I don't have a 2nd row with 1 or 0 i can use (there is, though there are also disturbances in the values cauzed by calibration or seal splices), so its more like:

[ATTACH=CONFIG]639[/ATTACH]

T-Bag · February 22, 2011, 10:06:55 AM

You can generate the control column with a simple IF statement =IF(C1>Z1,1,0) where Z1 is the threshold.

There's probably a way of doing it without the column but I don't use Excel anymore. I use Origin occasionally (demonstrating undergraduate labs) but do the vast majority of my work in Mathematica, which would be much easier for data manipulation I'd have thought.

Benny · February 22, 2011, 01:46:03 PM

Indeed, that's what I was thinking. I've had a quick play but it was getting very ugly, so did it like;
[ATTACH=CONFIG]640[/ATTACH]

I defined data areas just to keep it simple, list is self explanatory, top=top marker etc. Sorry can't be of more help.

Luminance · February 22, 2011, 01:56:41 PM

Thanks for the suggestions guys, but I think that this way it will only cost more time for all the different collums.

But I think it can be usefull for the STDEV I also want on a few collumns, because this function doesn't work like the averageifs function.

Benny · February 22, 2011, 06:11:51 PM

should be an easy one line insert and go, could be easily macro'd if it's a standard output you get?

TwoBad · February 22, 2011, 07:08:15 PM

I'm on my laptop which has 2003 on so can't test this (my son is playing BC2 on my desktop, which has 2007).
But give this a try
=AVERAGEIFS(C369:C518;C369:C518;">"&P8;C369:C518;"<"&P9)

Luminance · February 23, 2011, 08:15:24 AM

Hehe, it took me a few min to notice the space (hadn't seen your edit).

But this is exactly what I meant, thank you very much, it works splendidly

TeaLeaf · February 23, 2011, 08:58:10 AM

Nice one Twobad, not seen a use for that function before!

Luminance · February 23, 2011, 04:38:58 PM

I did more then twice the amounth of averages of yesterday in less then 3 hours today ^^
Just in time 2, they want an update tomorow morning and I got a lot of conclusions done today, just in time

TwoBad · February 23, 2011, 05:10:05 PM

Glad i could help!