If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
min thats = 0
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 |
#3
|
|||
|
|||
min thats = 0
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 |
#4
|
|||
|
|||
min thats = 0
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 |
#5
|
|||
|
|||
min thats = 0
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 |
#6
|
|||
|
|||
min thats = 0 (NEED HELP)
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 |
#7
|
|||
|
|||
min thats = 0 (NEED HELP)
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 |
Thread Tools | |
Display Modes | |
|
|