How do I identify "between" as criteria??

[ldyke01]

HELP!!!

I have a large IF statement that identifies certain criteria. When the criteria is met, an average is performed on a certain column for all rows meeting all points of criteria. One piece of criteria I cannot find a way to designate is if the number is >3 and <16, or between 3 and 16. Below is the array I currently have....the portion that does not seem to work properly is the AND portion (column N is the week number calculated using the WEEKNUM function in the source table).

=IF(ISERROR(AVERAGE(IF((Report!$I$2:$I$1792=$A2)*(AND(Report!$N$2:$N$1792>3,Report!$N$2:$N$1792<16)),Report!$T$2:$T$1792))),"-",AVERAGE(IF((Report!$I$2:$I$1792=$A2)*(AND(Report!$N$2:$N$1792>3,Report!$N$2:$N$1792<16)),Report!$T$2:$T$1792)))

To simplify this further, in case there is an easier way, I want to figure a 12 week average of a specific column for all rows that I=A2. If there are none, I want a "-" instead of the #DIV/0! error. I am sure if I understood VBA, I could do this, but unfortunately I am VERY VBA illiterate. Below is my attempt at creating a BETWEEN custom function that will prompt for the Greater Than and Less than numbers.....it didn't work either....

Function Between()
xl = Application.InputBox(Prompt:= _
"Less Than", Type:=1)
xg = Application.InputBox(Prompt:= _
"Greater Than", Type:=1)
If xl > (Number1) And xg < (Number2) Then

Exit Function
End If

End Function

If someone could help me either correct the custom function, correct my formula so I can specify the week number range, or tell me a simpler way to average the past 12 weeks of data in column T that also equal A2 in column I, I would be forever in your debt (truly......today is my third day working on this!!! I'm at my wits end!!!)

[ldyke01]

Well, it's Monday afternoon and still no luck. Anyone have any suggestions? The men with that pretty white coat with really long arms on it are on their way as I type....

[MrShorty]

I didn't quite understand the condition for your moving average (what is in A2 and what is I?) so I can't comment on an easier way to do it.

If I were to define a function "between" it would look something like this:

Function BETWEEN(x, xlow, xhigh) as boolean
BETWEEN=(x>xlow) and (x<xhigh)
End Function

comments: I prefer to pass arguments rather than use input boxes in functions. If you use and input box, everytime Excell deems it necessary to recalculate the cell, you will be presented with the input box, which would get real irritating (to me).

When defining a function, at some point you have to set the function name equal to something. This is how the value (in this case TRUE or FALSE) is
returned to Excel

Note that I have excluded xlow and xhigh from the test. If x=xlow, the function will return FALSE for example. You may need to adjust this statement to get exactly what you want.

Now in your spreadsheet, you will have one cell with the value of interest (x), another cell with the lower limit (xlow), and a third cell with the upper limit (xhigh).

[ldyke01]

I is the column on a separate spreadsheet with a person's name in it multiple times and A2 is the person's name listed once on the sheet I am performing calculations on.

I need to prompt for the two variables because this will be a weekly report so the range of the 12 weeks will change each week. I am using WEEKNUM to calculate the week number for each row and only want to average the specified information for the 12 weeks before the week I am running the report. So for this week I would need to average the information in the column for those records with a week number between or equal to 4 and 16 (or between 3 and 17 if it is not possible to include the 4 and 16 in the results).

Hopefully that did not confuse things even more. Thanks for taking the time to help!!

[MrShorty]

If I understand how your datasheet is set up, I think you should be able to use the sumproduct function to do this. I just recently got introduced to this, and find it to be a pretty slick way to get sums based on complex criteria. I'm going to do this longhand to hopefully help you see how this works.

Find two blank columns in your spreadsheet (I'll use B and C).

In B1, enter the formula =a2=report!i1. You'll get a TRUE or FALSE
In C1, enter the formula =and(report!n1>=4,report!n1<=15). You'll get either true or false. You could put the 4 and 15 into two cells and reference those cells, if you wanted to.
Select B1 and C1 and copy down the columns to row 1792 (or whatever row you need to get to). Now you have two columns of TRUE FALSE 's. Column B tells us which rows have "Joe", Column C tells us which rows are between weeks 4 and 15 (inclusive). If you force the spreadsheet to do so, TRUE and FALSE become the numbers 1 and 0.
Now we use the SUMPRODUCT function to sum the product of the condition columns with the column T in the data sheet. In any blank cell enter the formula =sumproduct (--b1:b1792,--c1:c1792,report!t1:t1792)/12. The double dash before the TRUE/FALSE arrays forces Excell to recognize those as 1/0. Now you have a 12 week for weeks 4 through 15. I've assumed in this that there is only one entry per week per person. You could count the number of entries with the formula =sumproduct(--b1:b1792,--c1:c1792), or to get soemthing other than a 12 week average, put (high+1-low) in the denominator.

Just in case someone thinks I'm crazy, where the week test includes the max and min, 12 weeks is week 4 through 15. If I include week 16, then I have 13 weeks.

You can combine all of this into a single sumproduct function, by nesting the formulas for columns B and C into the final sumproduct function (doing this you have to use cntrl-shift-enter, as it becomes an array function). I did it this way to hopefully help you understand how to use the sumproduct function in this way.

[swatsp0p]

May I ask what you will do in January, February and March when the prior 12 weeks will include weeks 52,51,50... from the prior calendar year? I think you need to anticipate this if you plan on using this format for an extended period of time.

Just a thought....

[ldyke01]

Once I reach the end of the year, I am assuming I will have to use two "between" criteria as part of the larger formula. One to specify the higher range, and one to specify the smaller range (i.e. between 50 and 52 AND between 1 and 10). I should be able to figure out that part once I am able to do a between function that will prompt for the range I want each week, but until then, I'm pretty much dead in the water.