My missus has asked me to write a function for her in Excel. I don't
know how to use Excel very well. I do think that it should be possible
though.

-*Background:*-
I have a spreadsheet with 5 worksheets representing the working days of
the week. Each worksheet contains data pertaining to every occurance of
that day for as long as it has been recorded. Each day is broken up
into hourly data.

So for example, for Monday's worksheet, you would have

COL A..............COL B........COL C
9/JAN/06........5AM.........DATA
9/JAN/06........6AM.........DATA
9/JAN/06........7AM.........DATA
-...ETC....THEN...-
2/JAN/06........5AM.........DATA
2/JAN/06........6AM.........DATA
2/JAN/06........7AM.........DATA
-...ETC....-

We are trying to create a function, -on another worksheet called
Query-. The only way I can describe what we want it to do is to do it
in psuedocode. I hope someone out there can understand it and help me!
Here goes...

-PSUEDOCODE:-

-// some variables-
runningtotal = 0;
count = 0;
month = "jan";- // these variables will eventually be cells in
worksheet Query-
day = "Monday"
time = "6am";
average = 0;

-// complicated part-
FOR EACH row in worksheet day {
IF column A contains month AND columm B = time {
runningtotal = runningtotal + column C;
count = count +1;
} -// end of IF loop-
} -// end of FOR EACH loop-

-// so once those tallys are done do the following-
average = runningtotal / count;
print average to the cell;

-// end of function-

My head is going round in circles trying to figure out how to do this
in Excel. Hope you guys can help. Thanks.


--
justo316
------------------------------------------------------------------------
justo316's Profile: http://www.excelforum.com/member.php...o&userid=30266
View this thread: http://www.excelforum.com/showthread...hreadid=499355

Are you saying that you want to add the items in column C for a given
time (column B) and month (column A)? Are the times stored as text (eg
"7AM") or as Excel time?

Do you insert new rows at the top of each daily sheet, so that the
latest dates are always visible? (You show 9th Jan before 2nd Jan
2006).

Pete

Pete Wrote:
Are you saying that you want to add the items in column C for a given
time (column B) and month (column A)?

Yes that is correct.

Pete Wrote:
Are the times stored as text (eg
"7AM") or as Excel time?

I believe we can assume it is just text.

Pete Wrote:
Do you insert new rows at the top of each daily sheet, so that the
latest dates are always visible? (You show 9th Jan before 2nd Jan
2006).

Pete

I think I may have gotten that the wrong way round. They would probably
be descending. Does that make a difference? They can change it around if
it makes it easier.


--
justo316
------------------------------------------------------------------------
justo316's Profile: http://www.excelforum.com/member.php...o&userid=30266
View this thread: http://www.excelforum.com/showthread...hreadid=499355

First of all, your design is not optimal, as shown by the problem you
are facing. But now is not the time to improve the design.
What makes it relatively easy in this case is that you have a small
number of data sheets.
I am assuming they are called with the names of the week.
In the following I am assuming that the month is entered in B1 as a
number from 1 to 12 and the time zone is entered as text in C1. The
data are in rows starting from A2 and up to 200. Change the 200 to a
number large enough to accomodate all sheets.

The function to show the total amount for a single sheet (Monday) is:

=SUMPRODUCT(--(MONTH('Monday'!$A$2:$A$200)=B1)*--('Monday'!$B$2:$B$200=C1)*($C$2:$C$200))

Your final formula should be

=SUMPRODUCT(...)+SUMPRODUCT(...)+

five times, replacing 'Monday' with the names of each sheet in each
SUMPRODUCT().

HTH
Kostis Vezerides

vezerid Wrote:
First of all, your design is not optimal, as shown by the problem you
are facing. But now is not the time to improve the design.

Yes I agree the original design is not the best but this has been put
together quickly as their current reporting application does not
accomodate all the information needed sometimes.

vezerid Wrote:

What makes it relatively easy in this case is that you have a small
number of data sheets.
I am assuming they are called with the names of the week.

That is correct.

vezerid Wrote:

In the following I am assuming that the month is entered in B1 as a
number from 1 to 12 and the time zone is entered as text in C1. The
data are in rows starting from A2 and up to 200. Change the 200 to a
number large enough to accomodate all sheets.

Do you want me to change the format? Maybe I wasn't clear enough.
Column A stores the full date, column B has the time zone, and column C
has the data required to be averaged.

vezerid Wrote:

The function to show the total amount for a single sheet (Monday) is:

=SUMPRODUCT(--(MONTH('Monday'!$A$2:$A$200)=B1)*--('Monday'!$B$2:$B$200=C1)*($C$2:$C$200))

Your final formula should be

=SUMPRODUCT(...)+SUMPRODUCT(...)+

five times, replacing 'Monday' with the names of each sheet in each
SUMPRODUCT().

HTH
Kostis Vezerides

Thank you very much! I will play around with this tomorrow morning.
However, I was wondering if you could maybe step me through your
formula so that I may understand it better in order to adapt it to my
needs (my spreadsheet is a little more complicated than I have let on,
but all the main elements are there.


--
justo316
------------------------------------------------------------------------
justo316's Profile: http://www.excelforum.com/member.php...o&userid=30266
View this thread: http://www.excelforum.com/showthread...hreadid=499355

SUMPRODUCT(A1:A10, B1:B10) will essentially do:

sum = 0
for i = 1 to 10
sum = sum + A(i)*B(i)
next i

For your purposes, the same example as above could be written as
SUMPRODUCT(A1:A10*B1:B10)

So, as you can see, it makes a pairwise multiplication of arrays.
Examine now the three arrays multiplied:
They are virtual arrays. The sub-expression, for example,
(B2:B200=C1)
will produce a computed array of TRUE or FALSE, depending on whether
B(i)=C1. The -- turns T/F into a number (1/0).
So, as you see, for each row, we multiply 1/0 with 1/0 with the amount
in column C:C. If both are 1 then the corresponding row in C:C will be
included in the summation. In this way multiplication essentially
becomes conjunction (AND) in a logical expression.
I hope this is clear enough to show you the principle.

Only SUMPRODUCT behaves this way. For example, if you try
SUM(A1:A100*B1:B100)
you would need to array-enter it, i.e. by pressing Shift+Ctrl+Enter.
Thus, the formula I suggested could have been written with SUM()
instead of SUMPRODUCT(), everything else being the same, but you would
need this key combination for it to work.

I hope this helps a bit in understanding the logic.

And no, I don't want you to change any format, just the numbers in the
formula. Replace for example A200 with A500 if you have more rows.

HTH
Kostis Vezerides