I am trying to average the amount of time (Column Q) if there is a 1 in column AA by day.DATA

If I use formulas to review the data (table on the left) I get an accurate average min / LC. If I use a pivot table (on the right) I get incorrect averages (i.e. an average time when no LC occurred). enter image description here

How do I get the pivot table to give the correct averages without using PowerPivot or VBA?

  • I suspect you are including in your average the ones that have a "LC count" of -0-. move that count from the data section to either the columns or the filter section. If in the columns section, you should see averages for 0 and 1 (which should match your output). If you move to the filter you then select one from the filter and it should match your output. – gns100 Jun 12 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.