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.
Bookmarks