Help me write a function please?

[justo316]

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.

 

[Pete]

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

 

[justo316]

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.

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

I believe we can assume it is just text.

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.

 

[vezerid]

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

 

[justo316]

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.

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.

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.

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.

 

[vezerid]

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

 

[justo316]

Thank you so much! I have got it working somewhat....

The next problem I am having is because of the way the data is entered
I'll try give you an example.

A......................B..................C.....................D...................E..........
Date............Time Zone........Heading 1........Headin
2........Heading 3...
1/jan/06.......5am-6am...........data................data................data.......
1/jan/06.......6am-7am...........data................data................data.......
..etc..
Date............Time Zone........Heading 1........Headin
2........Heading 3...
8/jan/06.......5am-6am...........data................data................data.......
8/jan/06.......6am-7am...........data................data................data.......

It is a bit more complex than this but you get the idea. There ar
headings that get in the way of the calculation which stuff it up.
assumed that as long as the condition of the date and time were no
met, then the data in corresponding column C wouldn't be factored in
But I think it is, and because it is a string, it is causing problems.

Any ideas how to skip around those lines

 

[vezerid]

I don't know if you are still reading the post, sorry but I log in only
a few hours every day.
Your thinking is correct: since you do not encounter "sensitive" values
in the repeating header rows the suggested formula should work without
problems. What makes you think that it is not working?

Kostis Vezerides

 

[justo316]

.....because it says "#VALUE!" in the cell with your query in it. If I
then go and remove the headings from the daily sheets, then your query
works and adds up all the appropriate data.

Also, any idea on how to get an average instead of a sum?

 

[vezerid]

(If you are still reading this...)

Ahhhh! I understand why you get #VALUE!. The MONTH() function complains
when it encounters text. You will have to enclose it in the N()
function as follows:

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


To get the average: This SUMPRODUCT gives you the total quantity. You
have to divide it by the number of items found. This is the SUMPRODUCT
without the last component:
SUMPRODUCT(--(N(MONTH('Monday'!$A$2:$A$200))=B1)*--('Monday'!$B$2:$B$200=C1))

Thus, your average formula would be:
=SUMPRODUCT(--(N(MONTH('Monday'!$A$2:$A$200))=B1)*--('Monday'!$B$2:$B$200=C1)*­($C$2:$C$200))/=SUMPRODUCT(--(N(MONTH('Monday'!$A$2:$A$200))=B1)*--('Monday'!$B$2:$B$200=C1))


HTH
Kostis Vezerides

 

[justo316]

Yes I am still reading this thread!!!

The big average formula you gave me doesn't seem to work. Excel say
there's an error in it and my head hurts at just the thought of tryin
to figure out what is wrong with it.....

I did make a couple small changes (one at a time) but it didn't hel


=3DSUMPRODUCT(--(N(MONTH('Monday'!$A$2:$A$200))=3DB1)*--('Monday'!$B$2:$B$*2
00*=3DC1)*=AD(*Monday'!*$C$2:$C$200))/=3DSUMPRODUCT(--(N(MONTH('Monday'!$A$2:$A$200))=
=3DB1)*--('Monday'!$B$2:$B$200=3DC1))

Actually I remember from your last formula (that just gave a sum, bu
got tied up with the text) I had to add the reference to the "query
worksheet to get it to work. Maybe I have to do the same to this one