Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Question about averages... Possibly Nested loops...
Hello,
I don't know if this is reallya difficult question, but its got me stumped. What I am trying to achieve is to calculate a monthly average for individual users. There is a table that looks like this: User 23/04/2007 24/04/2007 Mike 41% 52% Micheal 39% 45% Jason 58% 55% The first column contains the user in question, and the subsequent columns the data for each day. The result is that all the data for one user is in one row. The only (big) problem is that new columns of data will be added on a daily, or quasi daily basis. This implies that i don't know how many columns would be added for one particular month (so, no predifined spacings for the calculations). The averages per user need to be calculated and formatted in the following format: User April May Mike 40% 45% Micheal 30% 50% Jason 50% 40% I couldn't for the life of me think of a way to do this with a worksheet function, and other than a hunch of needing nested loops or doing one loop per month per user, I'm stumped. Any help would be greatly appreciated! Thanks, Janos |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Question about averages... Possibly Nested loops...
The average function ingores empty cells. Therefroe, if you average more
columns it doesn't change the calculation. Pick the larrgest number of column you will need and average all the columns even if you don't use them. =average(a5:dd5) "Janos" wrote: Hello, I don't know if this is reallya difficult question, but its got me stumped. What I am trying to achieve is to calculate a monthly average for individual users. There is a table that looks like this: User 23/04/2007 24/04/2007 Mike 41% 52% Micheal 39% 45% Jason 58% 55% The first column contains the user in question, and the subsequent columns the data for each day. The result is that all the data for one user is in one row. The only (big) problem is that new columns of data will be added on a daily, or quasi daily basis. This implies that i don't know how many columns would be added for one particular month (so, no predifined spacings for the calculations). The averages per user need to be calculated and formatted in the following format: User April May Mike 40% 45% Micheal 30% 50% Jason 50% 40% I couldn't for the life of me think of a way to do this with a worksheet function, and other than a hunch of needing nested loops or doing one loop per month per user, I'm stumped. Any help would be greatly appreciated! Thanks, Janos |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Question about averages... Possibly Nested loops...
To me it would be easier if you redesign you structure so you were adding
records rather than fields. User DateIn Usage Mike 23/04/2007 41 Mike 24/04/2007 52 ....etc You can then SUMIF, COUNTIF, AND etc or SUBTOTALs etc to get the data/report. You could also use SQL to query it. NickHK "Janos" wrote in message ... Hello, I don't know if this is reallya difficult question, but its got me stumped. What I am trying to achieve is to calculate a monthly average for individual users. There is a table that looks like this: User 23/04/2007 24/04/2007 Mike 41% 52% Micheal 39% 45% Jason 58% 55% The first column contains the user in question, and the subsequent columns the data for each day. The result is that all the data for one user is in one row. The only (big) problem is that new columns of data will be added on a daily, or quasi daily basis. This implies that i don't know how many columns would be added for one particular month (so, no predifined spacings for the calculations). The averages per user need to be calculated and formatted in the following format: User April May Mike 40% 45% Micheal 30% 50% Jason 50% 40% I couldn't for the life of me think of a way to do this with a worksheet function, and other than a hunch of needing nested loops or doing one loop per month per user, I'm stumped. Any help would be greatly appreciated! Thanks, Janos |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Question about averages... Possibly Nested loops...
Hello Joel,
Thank you for your swift reply, I realise that the Average function does that, my problem is that i won't know when one month will end and the other will start. ie the total columns for april might be more or less than the number of days in april, and may will follow straight after. My problem stays, how do I count the april columns? A thought I had was to count with a macro the number of columns for any one month and derive the address of the first and last cell, subsequently feeding it into the average function. Question is, how would I get about counting the size of the range, and its starting and ending cell? Janos "Joel" wrote: The average function ingores empty cells. Therefroe, if you average more columns it doesn't change the calculation. Pick the larrgest number of column you will need and average all the columns even if you don't use them. =average(a5:dd5) "Janos" wrote: Hello, I don't know if this is reallya difficult question, but its got me stumped. What I am trying to achieve is to calculate a monthly average for individual users. There is a table that looks like this: User 23/04/2007 24/04/2007 Mike 41% 52% Micheal 39% 45% Jason 58% 55% The first column contains the user in question, and the subsequent columns the data for each day. The result is that all the data for one user is in one row. The only (big) problem is that new columns of data will be added on a daily, or quasi daily basis. This implies that i don't know how many columns would be added for one particular month (so, no predifined spacings for the calculations). The averages per user need to be calculated and formatted in the following format: User April May Mike 40% 45% Micheal 30% 50% Jason 50% 40% I couldn't for the life of me think of a way to do this with a worksheet function, and other than a hunch of needing nested loops or doing one loop per month per user, I'm stumped. Any help would be greatly appreciated! Thanks, Janos |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Question about averages... Possibly Nested loops...
Hello NickHK,
I agree it would be easier, sadly the format is predefined and I cannot change it. I can pivot it around in a temporary location if i wanted to, but the input and output are (sadly) fixed. Janos "NickHK" wrote: To me it would be easier if you redesign you structure so you were adding records rather than fields. User DateIn Usage Mike 23/04/2007 41 Mike 24/04/2007 52 ....etc You can then SUMIF, COUNTIF, AND etc or SUBTOTALs etc to get the data/report. You could also use SQL to query it. NickHK "Janos" wrote in message ... Hello, I don't know if this is reallya difficult question, but its got me stumped. What I am trying to achieve is to calculate a monthly average for individual users. There is a table that looks like this: User 23/04/2007 24/04/2007 Mike 41% 52% Micheal 39% 45% Jason 58% 55% The first column contains the user in question, and the subsequent columns the data for each day. The result is that all the data for one user is in one row. The only (big) problem is that new columns of data will be added on a daily, or quasi daily basis. This implies that i don't know how many columns would be added for one particular month (so, no predifined spacings for the calculations). The averages per user need to be calculated and formatted in the following format: User April May Mike 40% 45% Micheal 30% 50% Jason 50% 40% I couldn't for the life of me think of a way to do this with a worksheet function, and other than a hunch of needing nested loops or doing one loop per month per user, I'm stumped. Any help would be greatly appreciated! Thanks, Janos |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Question about averages... Possibly Nested loops...
The number of columns can be counted by using the countif statement. You can
write Monthly columns = countif(<=4/30/07) - countif(<4/1/07) "Janos" wrote: Hello Joel, Thank you for your swift reply, I realise that the Average function does that, my problem is that i won't know when one month will end and the other will start. ie the total columns for april might be more or less than the number of days in april, and may will follow straight after. My problem stays, how do I count the april columns? A thought I had was to count with a macro the number of columns for any one month and derive the address of the first and last cell, subsequently feeding it into the average function. Question is, how would I get about counting the size of the range, and its starting and ending cell? Janos "Joel" wrote: The average function ingores empty cells. Therefroe, if you average more columns it doesn't change the calculation. Pick the larrgest number of column you will need and average all the columns even if you don't use them. =average(a5:dd5) "Janos" wrote: Hello, I don't know if this is reallya difficult question, but its got me stumped. What I am trying to achieve is to calculate a monthly average for individual users. There is a table that looks like this: User 23/04/2007 24/04/2007 Mike 41% 52% Micheal 39% 45% Jason 58% 55% The first column contains the user in question, and the subsequent columns the data for each day. The result is that all the data for one user is in one row. The only (big) problem is that new columns of data will be added on a daily, or quasi daily basis. This implies that i don't know how many columns would be added for one particular month (so, no predifined spacings for the calculations). The averages per user need to be calculated and formatted in the following format: User April May Mike 40% 45% Micheal 30% 50% Jason 50% 40% I couldn't for the life of me think of a way to do this with a worksheet function, and other than a hunch of needing nested loops or doing one loop per month per user, I'm stumped. Any help would be greatly appreciated! Thanks, Janos |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Question about averages... Possibly Nested loops...
Right... I think I answered my own question. I am using one loop per month,
with a counter and a placeholder that will record the last address. Since I know where I start, this gives me the range to average. I then write a worksheet function with the range to the workshet so that the user (and myself) can alwys see where the data comes from. The code looks something like this: Dim r1 As Range 'first placeholder range Dim MultiRange As Range 'final multirange Dim counter As Integer 'counter Dim Address1 As String 'Address placeholder Dim Address2 As String 'Address placeholder Dim AddressTotal As String 'Address placeholder counter = 0 Address1 = "R7C2" 'count April For Each r1 In ActiveSheet.Range("B6:IU6").Cells If Not IsEmpty(r1.Value) Then If r1.Value "1/4/2007" And r1.Value < "31/04/2007" Then counter = counter + 1 Address2 = r1.Offset(1, 0).Address(ReferenceStyle:=xlR1C1) End If End If Next 'creates the Address for April AddressTotal = Address1 & ":" & Address2 'Pastes the function Range("C20").Value = "=Average(" & AddressTotal & ")" 'Sets the start of May Address1 = "R7C" & counter + 2 "Janos" wrote: Hello, I don't know if this is reallya difficult question, but its got me stumped. What I am trying to achieve is to calculate a monthly average for individual users. There is a table that looks like this: User 23/04/2007 24/04/2007 Mike 41% 52% Micheal 39% 45% Jason 58% 55% The first column contains the user in question, and the subsequent columns the data for each day. The result is that all the data for one user is in one row. The only (big) problem is that new columns of data will be added on a daily, or quasi daily basis. This implies that i don't know how many columns would be added for one particular month (so, no predifined spacings for the calculations). The averages per user need to be calculated and formatted in the following format: User April May Mike 40% 45% Micheal 30% 50% Jason 50% 40% I couldn't for the life of me think of a way to do this with a worksheet function, and other than a hunch of needing nested loops or doing one loop per month per user, I'm stumped. Any help would be greatly appreciated! Thanks, Janos |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested hlookup/vlookup (possibly just an if statement though?!?) | Excel Discussion (Misc queries) | |||
Many Nested loops | Excel Programming | |||
nested for loops and end for | Excel Programming | |||
Problem with data using IF and Nested IF statements possibly??? | Excel Discussion (Misc queries) | |||
Help on nested loops | Excel Programming |