min thats = 0

I Have 52 work sheets each sheet = one week, on each sheet cell W265 IS ZERO
& on a seperate work sheet im trying to figure out min I tried alot of
different formulas for example =min('week 1:week 52'!,w265) the cell im using
will read zero until data is entered & still reads zero so i tried 0 in
formula & get a ref error can some one please help with this

The best way would be if you change the data so that W265 has a blank
instead of zero before anything is entered.


Otherwise you might be able to use something like


=MIN(IF(N(INDIRECT(MySheets&"!W265"))0,N(INDIRECT (MySheets&"!W265"))))

where MySheets is a named range containing the names of all the sheets you
are using so for a year that would be a list of 52 sheets

--


Regards,


Peo Sjoblom


"Mike" wrote in message
...
I Have 52 work sheets each sheet = one week, on each sheet cell W265 IS
ZERO
& on a seperate work sheet im trying to figure out min I tried alot of
different formulas for example =min('week 1:week 52'!,w265) the cell im
using
will read zero until data is entered & still reads zero so i tried 0 in
formula & get a ref error can some one please help with this

The formula needs to be entered with ctrl + shift & enter

--


Regards,


Peo Sjoblom


"Peo Sjoblom" wrote in message
...
The best way would be if you change the data so that W265 has a blank
instead of zero before anything is entered.


Otherwise you might be able to use something like


=MIN(IF(N(INDIRECT(MySheets&"!W265"))0,N(INDIRECT (MySheets&"!W265"))))

where MySheets is a named range containing the names of all the sheets you
are using so for a year that would be a list of 52 sheets

--


Regards,


Peo Sjoblom


"Mike" wrote in message
...
I Have 52 work sheets each sheet = one week, on each sheet cell W265 IS
ZERO
& on a seperate work sheet im trying to figure out min I tried alot of
different formulas for example =min('week 1:week 52'!,w265) the cell im
using
will read zero until data is entered & still reads zero so i tried 0 in
formula & get a ref error can some one please help with this

For OP

If the sheets are named Week1 through Week52 a list can easily be made on a
separate worksheet.

Type Week1 in A1

Right-click A1 and drag down to A52.

Release button and "Fill Series".

Name that range as MySheets as Peo suggests.

If not named as such you can use a macro to get a list of sheetnames on a
new sheet named "List"

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub


Gord Dibben MS Excel MVP

On Wed, 11 Nov 2009 15:47:38 -0800, "Peo Sjoblom" wrote:

The best way would be if you change the data so that W265 has a blank
instead of zero before anything is entered.


Otherwise you might be able to use something like


=MIN(IF(N(INDIRECT(MySheets&"!W265"))0,N(INDIREC T(MySheets&"!W265"))))

where MySheets is a named range containing the names of all the sheets you
are using so for a year that would be a list of 52 sheets

--


Regards,


Peo Sjoblom


"Mike" wrote in message
...
I Have 52 work sheets each sheet = one week, on each sheet cell W265 IS
ZERO
& on a seperate work sheet im trying to figure out min I tried alot of
different formulas for example =min('week 1:week 52'!,w265) the cell im
using
will read zero until data is entered & still reads zero so i tried 0 in
formula & get a ref error can some one please help with this

Hi Mike

-The sheetnames are 'Week 1', 'Week 2' with a space between the week and
number
--Try with 2 3 sheets and once through change that to 52.

Try the below version
=IF(SUM('Week 1:Week 52'!A1),MIN('Week 1:Week 52'!W265),"")


'If zero is by default and you need to ignore them try the below version
=SMALL('Week 1:Week 52'!A1,SUMPRODUCT(COUNTIF
(INDIRECT("'Week " & ROW(1:52) &"'!W265"),"0"))+1)

If this post helps click Yes
---------------
Jacob Skaria


"Mike" wrote:

I Have 52 work sheets each sheet = one week, on each sheet cell W265 IS ZERO
& on a seperate work sheet im trying to figure out min I tried alot of
different formulas for example =min('week 1:week 52'!,w265) the cell im using
will read zero until data is entered & still reads zero so i tried 0 in
formula & get a ref error can some one please help with this

lets start again i have 52 worksheets, named week 1 to week 52
In each week cell w265 is what i need to get (min) & average on a seperate
worksheet for year end totals but cells w265 all read 0% until data is
entered for each week, Now when i try to formulate i get a name,ref, or value
error & believe me i tried alot of combinations to try & resolve this can any
one please help & make it simple

Well, here's my response to you from a couple of days ago:

"...
What formulae have you tried? I would suggest these:

=MIN(Sheet1:Sheet52!W265)

and

=AVERAGE(Sheet1:Sheet52!W265)

but instead of having 0% in those cells on sheets where there is no
data, I would suggest you return an empty string "".
...."

which is what Peo suggested in his reply to you above. If your cells
contain 0% on sheets where there is no data, then this will be picked
up as the minimum and will also be counted within the average.

Suppose you have:

your_formula

in W265, then all you need to do is to change this to:

=IF(your_formula=0,"",your_formula)

You can do this in one operation by grouping sheets 1 to 52 together
and then making the change once (to them all) and then un-grouping the
sheets.

Note also, that if your sheet names have spaces in them then you will
need to put apostrophes in the formula like so:

=MIN('Week 1:Week 52'!W265)

and

=AVERAGE('Week 1:Week 52'!W265)

Hope this helps.

Pete



On Nov 12, 11:38*pm, Mike wrote:
lets start again i have 52 worksheets, named week 1 to week 52
In each week cell w265 is what i need to get (min) & average on a seperate
worksheet for year end totals but cells w265 all read 0% until data is
entered for each week, Now when i try to formulate i get a name,ref, or value
error & believe me i tried alot of combinations to try & resolve this can any
one please help & make it simple