0

I have an Excel file for which every line is an observation of a range of species. Observations are made by 315 different cameras (sample points), each of which was set to collect data for a range of 5-38 days (I have the number of survey days for each of the points recorded).

I need to get an average number of observations by effort: 

 (number of cameras set * number of days set)

I can get this average number easily, but in order to run an ANOVA on the average number of observations by effort for each species observed I need all of the values, including the days where no species was observed.

I tried PivotTables to get the number of observations for each species by camera and survey day. The problem is, on days when no species was observed, there is no entry for the day. I thought of fixing this by adding dummy lines with 0s for all of these days, but with 315 points this will take a lot of time and have a high chance of error.

Any ideas of a better way to do this?

pnuts
  • 58,317
  • 11
  • 87
  • 139
abmiller8
  • 33
  • 4

2 Answers2

1

If you right click the PivotTable, and then click "PivotTable options", there is a box that says "For empty cells show:" and you can select to put 0 in to the cells with no value recorded.

(I think this is what you're asking. I may be totally off base, though.)

phroureo
  • 377
  • 3
  • 16
  • Thanks for the suggestion, I hadn't found that option yet. Unfortunately it doesn't solve my problem. I need dummy lines inserted, so that each survey day has a line, which would then be equal to 0. Currently, if an animal was only observed on days 1, 5, 9, and 14 out of a 21 day period, there are only lines for days 1, 5, 9, and 14. I need lines for each of the 21 days so that the numbers will be averaged including the 0-sighting days. – abmiller8 Nov 06 '15 at 20:45
  • Help me wrap my head around how your data is organized: Each row is an animal, and each column is a day, correct? – phroureo Nov 06 '15 at 21:30
  • Yes, each row is an observation of a species, made at a specific sample point at a given time. So, each row is a species with corresponding covariates including sample point, phase (before/during/after), zone (control/ontrail/offtrail), date and time of observation, number of individuals of the species observed. the same sample point is used multiple times throughout the one-year study, and i have a column that has the sample point ID and a column that has the sample point ID combined with survey period, so I can use either. – abmiller8 Nov 07 '15 at 13:37
  • If you autocomplete the list of dates (type out the first date and then click the bottom right corner of the cell and drag down to the ending date), then is there any way you could use a VLOOKUP or HLOOKUP to find relevant info? I'm still struggling to picture your data in my head. Could you maybe put a few sample rows (doesn't necessarily have to use real data) in the original question? – phroureo Nov 09 '15 at 15:37
0

Go to the pivot field in the field list> columns(days for you)> click on down arrow on your field> Field_Settings> Layout Tab> Click on Show fields with no Data

DS R
  • 235
  • 2
  • 13