Data analysis and MEAL: spreadsheets, qualitative data and checking calculations

1What AI brings to MEAL

MEAL teams spend a lot of time on repetitive tasks: cleaning files, writing formulas, producing tables, coding open answers, drafting summaries. AI can speed up these tasks:

• Formulas and scripts: writing a spreadsheet formula (XLOOKUP, COUNTIFS, pivot tables), a Python or R script, a query in a data collection tool, and explaining them line by line.
• File analysis: Claude and Gemini can read a CSV or Excel file, run calculations in a code environment and produce tables or charts (depending on the version and plan).
• Qualitative data: proposing a coding frame, coding answers, grouping by theme, extracting representative quotes.
• Writing: turning results into findings, then into recommendations.

But MEAL data is often personal (household lists, named surveys). The rule from week 3 applies: only anonymised and aggregated data, or work on a fictitious sample with the same structure.

2Formulas and scripts: the fictitious data method

A safe and effective method to automate an analysis without exposing data:

• 1. Describe the structure of your file (column names, types, examples of invented values) or create 10 fictitious rows with the same structure.
• 2. Ask for the formula or script: "Write a formula that counts the number of female-headed households that received at least 2 distributions, by district."
• 3. Test on the fictitious data, where you know the right result.
• 4. Apply to your real data, in your spreadsheet or on your computer.
• 5. Check with an independent calculation (manual filter, pivot table).

With Gemini in Google Sheets or the AI features of other spreadsheets, the AI acts directly in the file: check that the plan is professional and authorised for this data.

AI writes the formula, you run it on your data, you check the result. Personal data does not leave your tools.

3Checking calculations and statistics

Language models can make mistakes in mental arithmetic, especially on long series. When the assistant actually runs code, the calculations are reliable, but the interpretation can be wrong. Essential checks:

• Denominators: a percentage of what? Respondents, surveyed households, the target population? Are missing answers excluded?
• Sample: a result from 250 surveyed households does not apply to the whole population without an appropriate sampling method. Ask for the margin of error.
• Mean or median: for income or expenditure, a few extreme values distort the mean.
• Correlation is not causation: "households that received aid have a better food consumption score" does not prove that the aid is the cause without a comparison group.
• Disaggregation: sex, age, disability, district; check that the subtotals add up to the total.

Calculation. A simple random sample of 384 households: margin of error of about ±5% at a 95% confidence level for a proportion close to 50%. If the AI states "62% of households in the province" from 60 households chosen by convenience, the conclusion is not representative: this must be said.

4Qualitative data: rigour and traceability

For focus group transcripts or community feedback (anonymised):

• Coding frame: ask for a proposal based on your evaluation questions, then validate it as a team.
• Coding: have the extracts coded with the validated frame, requiring for each theme the number of extracts and two or three exact quotes with their reference (group, anonymised location).
• Check: recode a sample yourself (for example 20%) and compare. If agreement is low, revise the frame or the instruction.
• Nuances: explicitly ask for minority and contradictory views; AI tends to summarise towards the majority.
• Quotes: check that each quote exists word for word in the source; a rephrased quote must not be presented in quotation marks.

NotebookLM or a Claude Project with the transcripts as sources make this work easier, with answers linked to the source passages.

5Feedback and complaints mechanisms

Feedback and complaints mechanisms sometimes receive hundreds of messages (phone lines, SMS, boxes, messaging apps). AI can help to:

• propose categories (information, request for assistance, complaint about quality, sensitive complaint);
• summarise monthly trends from aggregated and anonymised data;
• draft validated standard replies.

Strict limits:

• sensitive complaints (sexual exploitation and abuse, fraud, security) follow a confidential human channel and never go through an AI assistant;
• automatic classification of a message must never delay the handling of a serious complaint: a human reads incoming messages;
• people must know how their messages are handled.

6From results to recommendations

The most delicate step of an evaluation is moving from data to findings, then to recommendations. AI can propose a clear structure (finding, evidence, conclusion, recommendation, owner, deadline), but:

• each finding must be supported by at least two sources or methods (triangulation);
• recommendations must be realistic for the organisation: budget, capacities, timeline;
• evaluation criteria (for example the OECD DAC criteria: relevance, coherence, effectiveness, efficiency, impact, sustainability) structure the analysis.

Then ask for a critique: "Which conclusions of this report are not sufficiently supported by the data presented?" This adversarial review clearly improves quality.