Blanking / Discounting zero values in ranges

Thanks for all the help I've had so far from the forums - one more question!

What I have so far is a front sheet of data of numbers of crimes in a given time period (fortnight) arranged in columns according to crime type.

Each column of data (crime series) is then taken into separate and subsequent worksheets as a row using the OFFSET function and the mean, standard deviation etc. calculated. This is then used to update a dynamic statistical process control chart using the latest n (user selectable) periods and where the row is a named series.

I would like the spreadsheet to be as user-friendly (idiot-proof!) as possible and so would like others to only have to input figures on the initial data sheet and have everything else automated. The problem is that where there is no data to drag through from the data sheet to the individual worksheets (i.e. numbers of crimes happening in the future) I end up with a zero as the value, and eventually zeros for the mean/standard deviation. This in turn corrupts the control chart.

Is there a way in which I can copy the various formulae across the various rows, but have the cells show as blank if no data is available, and also have the chart plot the latest n periods of data (also discounting the zeros/blanks)?

Hope this makes sense,

Robert

Using IF(OFFSET(......)...) shouldn't make a difference, since COUNTA() counts both 0 and "" values. In fact, I tested it and it works fine.

Also, the formula you're using to define your dynamic range seems fine. Check to make sure that it's entered correctly in the 'Refers to' box.

If you're still having problems, post back.

Originally Posted by RobPot

Thanks but I already have the charts set up.
Could the problem be too many Offsets?

1) From the initial data sheet (called 'Data Sheet') I use the following formula to bring the data into rows:
=IF(OFFSET('Data Set'!$B$2,COLUMN()-COLUMN($B$2),0)="","",OFFSET('Data Set'!$B$2,COLUMN()-COLUMN($B$2),0))
-This works fine.

2) On the individual crime type sheets I base a chart on a Named Range 'ChartLength' which allows the user to select how many periods to show, and a dynamic range: 'Total_Crime_Tactical_Period' (so, for example on the worksheet entitled 'Total Crime':
=OFFSET('Total Crime'!$B$2,0,COUNTA('Total Crime'!$2:$2)-1,1,-MIN(ChartLength,COUNTA('Total Crime'!$2:$2)-1))

I then have a series of offsets to rows below to chart the various series.

the problem seems to be that since I've added the =IF(OFFSET="","", line the chart no longer finds the 'Total_Crime_Tactical_Period' range.

Anyone able to offer any light?

Thanks

Robert

Change the formula to the following...

=IF(OFFSET('Data Set'!$E$2,COLUMN()-COLUMN($B$5),0)="",#N/A,OFFSET('Data Set'!$E$2,COLUMN()-COLUMN($B$5),0))

The #N/A value is ignored and therefore won't affect the chart. Also, if you'd like, you can hide the #N/A values by using Conditional Formatting. Post back if you need help with that.

Hope this helps!

Originally Posted by RobPot

Thanks but I already have the charts set up.
Could the problem be too many Offsets?

1) From the initial data sheet (called 'Data Sheet') I use the following formula to bring the data into rows:
=IF(OFFSET('Data Set'!$B$2,COLUMN()-COLUMN($B$2),0)="","",OFFSET('Data Set'!$B$2,COLUMN()-COLUMN($B$2),0))
-This works fine.

2) On the individual crime type sheets I base a chart on a Named Range 'ChartLength' which allows the user to select how many periods to show, and a dynamic range: 'Total_Crime_Tactical_Period' (so, for example on the worksheet entitled 'Total Crime':
=OFFSET('Total Crime'!$B$2,0,COUNTA('Total Crime'!$2:$2)-1,1,-MIN(ChartLength,COUNTA('Total Crime'!$2:$2)-1))

I then have a series of offsets to rows below to chart the various series.

the problem seems to be that since I've added the =IF(OFFSET="","", line the chart no longer finds the 'Total_Crime_Tactical_Period' range.

Anyone able to offer any light?

Thanks

Robert