meed help start with 6.
Cattle Data 1. Select cell H1. Go to Data Data Validation Data Validation. Use the drop-down list under "Allow:" to select "List". Select cells K1:K2 for the "Source" field. 2. Using a similar process, create a drop-down list in cell 42 using cells L1:L.51 as the soure. 3. Using a similar process, create a drop-down list in cell H3 using cells M1:M2 as the sounee. 4. Hide colums K:M. 5. Select "FIRST OF JAN" in H1; select "ALABAMA" in H2; and select "CATTLE, ON FERD - IN. VENTORY" in H3. 6. In cell H4, calculate the average value from the "Value" column (E2:E7280) for the period, otate, and data item selected in cells H1:H3. 7. Set cells J7: J106 equal to cell H4. 8. In cells H7 : H106, retrieve the value from the "Value" column associated with the period, state, and data item selected in cells H1 :H3. Both the AVERAGEIFS and StMIFS functions will work for this At this point the chari in your file should look like the oae shown in figure 1 . Note all of the values in the "Annual" series. This is misleading because the values were not actually 0 , they were nonexistent. Thus, we want to constrain the "Annual" and "Average" srics on the chart to just the time periods when data was avallable. We will do this by following the tanka below. Figure 1: Chart Example 1 9. In the "Data Available" column, nse the CoUNIIFS function to count the number of observations corresponding to the period, state, and data item in cells H1:H3 for each year in cells G7: G106. If done correctly, the function should produce one of two values: 0 or 1 . 10. To suppress the 0 values in the chart's "Annual" series, we need to suppress the 0 values (if you used the SUMIFS function) or the \#DIV/0! values (if you used the AVERAGEIFS function) in cells H7:H106. To do this, we want to replace them with N//A values using the NAC) function. For example, if you use =NAO in cell I, it will return the value \#N/A. Thus, what we want in cells H7:H106 are functions that will return the value from the "Value" column if there was an observation for the particular period, state, data item, and year; or return N/A if there was not an observation. Adjust your formulas in cells H7:H106 to accomplish this. 11. Perform a similar adjustment to cells J7:J106 : They should display the average value if there was an observation for the particular period, state, data item, and year; or return N/A if there was not an observation. 12. Hide columns I:J and select "NEW MEXICO" as the state. Your chart should now look like figure 2. CATTLE, ON FEED - INVENTORY, FIRST OF JAN, NEW MEXICO Figure 2. Final Chart Cattle Data 1. Select cell H1. Go to Data Data Validation Data Validation. Use the drop-down list under "Allow:" to select "List". Select cells K1:K2 for the "Source" field. 2. Using a similar process, create a drop-down list in cell 42 using cells L1:L.51 as the soure. 3. Using a similar process, create a drop-down list in cell H3 using cells M1:M2 as the sounee. 4. Hide colums K:M. 5. Select "FIRST OF JAN" in H1; select "ALABAMA" in H2; and select "CATTLE, ON FERD - IN. VENTORY" in H3. 6. In cell H4, calculate the average value from the "Value" column (E2:E7280) for the period, otate, and data item selected in cells H1:H3. 7. Set cells J7: J106 equal to cell H4. 8. In cells H7 : H106, retrieve the value from the "Value" column associated with the period, state, and data item selected in cells H1 :H3. Both the AVERAGEIFS and StMIFS functions will work for this At this point the chari in your file should look like the oae shown in figure 1 . Note all of the values in the "Annual" series. This is misleading because the values were not actually 0 , they were nonexistent. Thus, we want to constrain the "Annual" and "Average" srics on the chart to just the time periods when data was avallable. We will do this by following the tanka below. Figure 1: Chart Example 1 9. In the "Data Available" column, nse the CoUNIIFS function to count the number of observations corresponding to the period, state, and data item in cells H1:H3 for each year in cells G7: G106. If done correctly, the function should produce one of two values: 0 or 1 . 10. To suppress the 0 values in the chart's "Annual" series, we need to suppress the 0 values (if you used the SUMIFS function) or the \#DIV/0! values (if you used the AVERAGEIFS function) in cells H7:H106. To do this, we want to replace them with N//A values using the NAC) function. For example, if you use =NAO in cell I, it will return the value \#N/A. Thus, what we want in cells H7:H106 are functions that will return the value from the "Value" column if there was an observation for the particular period, state, data item, and year; or return N/A if there was not an observation. Adjust your formulas in cells H7:H106 to accomplish this. 11. Perform a similar adjustment to cells J7:J106 : They should display the average value if there was an observation for the particular period, state, data item, and year; or return N/A if there was not an observation. 12. Hide columns I:J and select "NEW MEXICO" as the state. Your chart should now look like figure 2. CATTLE, ON FEED - INVENTORY, FIRST OF JAN, NEW MEXICO Figure 2. Final Chart
