Most of my friends are quite capable of doing some basic formula with Excel / OpenOfficeCalc, but when it comes to routine works that require same formulas to process daily/weekly/monthly whatsoever data, they tend to re-create the formula again and again instead re-use/re-cycle the formulas.
Well it’s time for some efficiency example.
Suppose we are in some company that sells their products through branches and agents, we have a task to recapitulate weekly data, in this example those files are data-week1.xls and data-week2.xls
as shown below
data-week1.xls
data-week2.xls
We need to have a recapitulation of Sales, Cost, and Agent Fee
Create a new file to recapitulate data in those two files, here we choose the SUMIF syntax for this task
Lets name it 02_summary-weeks.xls
02_summary-weeks.xls : Recap Total Cost
We just recapitulated the Total Cost for Week 1, yes we can do the similar things to Sales & Agent Fee
But instead of doing the rather lengthy way, lets take a shortcut.
- Copy the formula from B4:B11 to C4:C11
02_summary-weeks.xls : Copy Formula from Cost to Sales
Notice that the formula still referring to the same cells ($E$2:$E$101) the Sales columns in data-week1.xls, we need those $E switched to $F
- Block cells range C4:C11 then do a Find and Replace, Find $E$ replace with $F$, the Total Sales will be created
02_summary-weeks.xls : Total Sales Find and Replace
Do similar things to Total Agent Fee, and we’ll have Week-1 recapitulaztion report.
How about week 2 ? not so much different
- Copy cells range B4:D11 to E4:G11
- Both files (data-week1.xls & data-week2.xls) must be opened
- Block cells range E4:G11 then do a Find and Replace again , Find data-week1.xls replace with data-week2.xls
02_summary-weeks.xls : Week 2 - Block Cells
Voila Week2 recapitulation is done
02_summary-weeks.xls : Recapitulation Done
You can do a lot with Find and Replace formulas, the application depends on your creativity
Get the above example files here
Tags: Cells, Excel, Excel Formula, Find, Find and Replace, Formula, OpenofficeCalc, Recap, Recapitulation, Replace, Sumif, Summary
