Join data
Why did sales and shipment totals increase after a join?
Sum sales and shipments separately for each store, week and product, then join the totals to avoid counting the same quantities more than once.
Make spreadsheet work simpler
Match customer lists, find missing values, and combine spreadsheets. See how to solve everyday Excel problems with worked examples in JOIN SHEET.
Clean up and summarize data
How do you keep passing projects from a numeric 0/1 checklist?
Use numeric 1 for passed and 0 for failed or untested checks. Keep projects whose minimum for both checks is 1. Every required part and check value must be present.
Clean up and summarize data
How do you split shipping costs in proportion to item value?
Split a shipment's cost according to each item's share of the purchase total. Check the total first and update it whenever the items change.
Append sheets
Append tables and keep one copy of identical rows
Combine files and keep one copy of each identical row. Records with different amounts or other values stay in the result.
Clean up and summarize data
How do you average daily totals from individual call records?
Sum call minutes for each date, then average those daily totals across the loaded period. Decide whether days without records belong in the denominator.
Clean up and summarize data
How do you calculate an average without zero values?
Exclude zeros used for missing measurements before calculating the average. Keep valid negative readings and check the count of included records.