Locate a max from range defined in other sheet in workbook

**yasmine26** · 06-10-2008, 03:25 PM

Hi!
Here my problem i would like to know if its possible (even if its not, is there anyway to make it slightly
easier) to do:
There we go:
in my workbook i have 3 sheets named "time" "final" "push". in the sheet thats named "final" ihave in cells B2 and C2 2 values that correspond to a type interval (lets say 75-378)
in the sheet "time" each row represents 1 sec, starting at one.
what i would like to do is locate the interval defined by the 2 values on "final" sheet
and find the min max and value in column S of the "time" sheet.
i.e if B2 and c2 said 78 and 378 it would go on "time" sheet select the range of S78-S378 and find the mean, the max and the average
and report them in sheet "final" in cells E2 F2 G2 respectively
What would be optimal is that there was a way to perhaps loop so that it can do the same thing with the range defined by the values in B3 and C3.

Thank you sooooo much!

**NBVC** · 06-10-2008, 03:31 PM

Try these:

=MIN(INDEX(Time!S:S,Final!B2):INDEX(Time!S:S,Final!C2))

=MAX(INDEX(Time!S:S,Final!B2):INDEX(Time!S:S,Final!C2))

=AVERAGE(INDEX(Time!S:S,Final!B2):INDEX(Time!S:S,Final!C2))

**yasmine26** · 06-10-2008, 03:40 PM

sorry my skills are really limited
is that all i should copy in the module?

**yasmine26** · 06-10-2008, 03:45 PM

i think i get it let me try

**NBVC** · 06-10-2008, 03:46 PM

These are formulas that you would place directly in the respective cells where you want the results to appear...E2, F2, G2.

This is not VBA code so no modules needed.

**yasmine26** · 06-10-2008, 03:51 PM

hey ,
its not working it returns 0 all the way down

**NBVC** · 06-10-2008, 03:58 PM

Are you sure you have data in sheet "Time" column S within a range defined by the values in B2 and C2 of sheet "final"?

Are the values in column S really Numbers? Select column S and go to Data|Text to Columns and just click Finish? Does that fix it?

**yasmine26** · 06-11-2008, 08:47 AM

it actually gives me an error message about circular reference?

**NBVC** · 06-11-2008, 08:51 AM

You can't place the formula in the entire column, Time!S:S or in cells, Finall!B2 or in Final!C2

If you still have problems... attach the document here.

**yasmine26** · 06-11-2008, 09:10 AM

maybe something alon the lines of
=MIN((time!(S(INDEX(Final!B2:C2,1,1):S(INDEX(Final!B2:C2,1,2))

this is giving me an error
would this look for the min in the sheet "time" (S number in cell B2:S number in cell C2)?

**NBVC** · 06-11-2008, 09:13 AM

Where exactly are you entering your formulas.

The formulas I gave you should work (unless your putting them somewhere in Column S of the Time sheet.

I guess you don't want to post your sheet, as I have asked for it twice...?

**yasmine26** · 06-11-2008, 09:50 AM

i have also tried
=MIN(Time!S(INDEX(Final!B2:C5,2,1)):S(INDEX(Final!B2:C5,2,2)))
and i get #NAME in the cell...

**NBVC** · 06-11-2008, 09:54 AM

I am trying to help... but if you refuse to listen or reply to the specific questions that I ask in order to diagnose the problem then I can't help... so I am dropping this and not wasting my time anymore.... sorry.

**yasmine26** · 06-11-2008, 10:18 AM

hey sorry i didnt get that ill attach the workbook (if i can since ive been denied before)
i enter the min in the e2,f2,g2 respectively
also i renamed the time sheet to 'rawdata"

edit: the workbook is 14.2 MB i think thats why i can't upload it...

**yasmine26** · 06-11-2008, 10:24 AM

the data text did not work
and to correct my earlier post min is in E2
max in F2
avg in G2
in the "final" sheet

**NBVC** · 06-11-2008, 10:24 AM

Since I am a nice guy and would like to try to help.. I will try once again...

Try cutting down the amount of data in the workbook.. I don't think we need to see everything.

Try also zipping it before you attach it.

**yasmine26** · 06-11-2008, 10:43 AM

ok give me 3 seconds

**yasmine26** · 06-11-2008, 11:08 AM

ok so there we goo i just made a new workbook because for some reason i dont have zip on this computer
for example in final sheet the first range would be from 14 to 176 i would like for the function to go on "rawdata" sheet in column S and found the min from rown 14-176
thanks once again for your patience