Excel 03 pivot table - working with yes/no and averages

Hi

I have a spreadsheet containing a pivot table, the data comes directly from an Access 2003 database.

The purpose of the table is to summarise informtion which is responses customers have made on evaluations of training courses and events.

As you can see on the attached screenshot, the table averages the numerical data (i.e. marks out of 4 for each section).

The problem I have is for a section called 'would you recommend?' - which a delegate would answer yes/no for (it's in yes/no format in the Access database).

For the life of me I don't know how to average this in the pivot table - what I need/would like is the average %age of people who said 'yes' (would recommend) but I don't know how.
As you can see on the attached it's currently counting all records, not just 'yes' or 'no'.

If anyone knows how I can do this I will be hugely grateful as I can then get this project completed.

Many thanks in advance

so the results in Jan show 8 - thats 8 Yes and Nos - so you need to have returned the number of Yes in Jan
So in the data set - you could add a extra column that counts the Yes and use that

Originally Posted by zbor

Go to Value field settings and change sum to average

Hi zbor - if I do that I get '#DIV/0!' in each cell

Originally Posted by etaf

so the results in Jan show 8 - thats 8 Yes and Nos - so you need to have returned the number of Yes in Jan
So in the data set - you could add a extra column that counts the Yes and use that

Hi estaf - thanks - do you mean add something in Access to do so?
The spreadsheet itself doesn't have the actual data, it's a pivot table directly relating to the Access database

Thanks etaf - I haven't used calculated fields in Excel before - if you're able to explain how to achieve it that would be superb.
I've attached an Excel file of the data (although the data itself comes directly from Access)
and the pivot spreadsheet

Many thanks

I would probably change the underlaying data within the Access data or query adding extra field, so you can count the number of YES for the would you recommend
you can then divide by the total number of responses

Thanks etaf - sorry to be a pain but how would I go about doing that?

Thanks etaf, much appreciated.
Makes sense - my query now I guess is, given the evaluation will be dynamic (i.e. added to all the time), how can I make the formulas for recommend yes etc automatically go into the new records, other than filling every cell which will make the spreadsheet huge?
(hope that makes sense!)

can you change the access table to include the columns ?

How do you query the access database with excel ?

hi, thanks again

I don't know a way of changing the access table to have it in.
The excel was done using data>import external data.

PS I can't now seem to group (as in the date field) in the spreadsheet?

not going very well......

is the access db in your control ?

do you query direct as a PT

Hi again

Yes the DB is one I made.
And yes (currently) Excel imports directly to the pivot table (i.e. you don't see all the actual data on another worksheet etc.)

thanks

if you own the DB why not do the calc in the DB table for Recommend = Yes and set to 1
then you can sum that field and divide by the records to get the % when you bring it back to excel

Hi - at the risk of sounding dense, how would I do that?

Thanks again, appreciate the help

That's great thanks so much.
I will go over it all this afternoon.
Really appreciate your help

any questions just come back to here - i will be online most of today UK GMT time...
I should have sent up email on this forum, so you can email me direct as well

Cheers - quick question - I notice the 'would your recommend' field in Access is yes/no (as is my original one) but in datasheet view shows as 0/-1 (rather than my original one which it shows as a tickbox - how did you achieve that?

Thanks

that must have been the way it was imported by Access to show TRUE and FALSE as -1 and 0 on the data but YES/NO on the design

keep with your old format - you dont want to much changed -

What do you have in your Table for that field -
Would you recommend and also the "can we use your comments"

all that would need changing is the IIF statement to read "YES" instead of TRUE

OK thanks.
Right I'm going through it all now so I understand it all.

When I try and do the pivot from scratch (using the DB you made), it won't let me group by date? Bit confused!
thanks

mine did group by Date OK
right click on the EventDate field in grey
Group and show detail
Group

all works just tried again

If you want to emial me the Access DB and spreadsheet (so its not in a public domain) and your allowed to re secruity / sensitivity of data
I cna PM my email address

Ah OK think I've sussed it - it won't let me (using a new xls that is) group if I have the date in page field, but is fine with it if put in the column section.
I'll crack on this afternoon but (fingers crossed!) think it should be fine, I'll let you know.
Cheers

etaf - I've now got it all sorted, just wanted to say many thanks for helping me

your very welcome .... glad to hear that