Convert Text to Numeric Excel 2010 Pivot Table

Hi All - I have exported a survey from Sharepoint to Excel pivot table. The survey answers are numbers only from the range of N/A, 0,1,2,3,4. I now want to apply some aggregate formulas on the survey answers using a pivot table in excel so i can view my answers in the "Data" area in the pivot table for each question and either sum or average them etc.
The problem is that the pivot table is storing the answers as "text" and does not "sum" them when i drag my fields to the data area. it only counts the responses.
I tried to create a "Calculated field" in the pivot table to use a formula =value(answer field) so that it converts my text "3" to a numeric "3" but its not working for me..
what can i do to fix this?
I want to do all this in the pivot table so when new answers are received in the share point - i just have to refresh my pivot table and everything gets refreshed and my average/sums of all answers get refreshed as well.

PLEASE HELP!!

Select the entire column which has numbers stored as text and do a text to columns( shortcut Alt, then A then E then F), replace N/A with zero in the column. Now make your pivot table.