Moving experience with a moving average
Truncating (not rounding) values in any spreadsheet
Cleaning up data in Excel
Deleting empty rows in Excel worksheets
Display negative time values in Excel
Q I produce a report of weekly statistics, with a column for each week's data over a year. In column A, I want a formula to calculate the average of the preceding four weeks, but I don't want to have to modify the formula every time I add the data for a new week. Should I use a macro to set this up?
- Paul Lang
A There's no need for a macro: you can use a moderately tricky formula to accomplish your purpose, and it will work in any spreadsheet.
Let's say your first week's numbers are in column B, as shown in Figure 1. 232a.tif: Figure 1: Calculate moving averages in 1-2-3, Quattro Pro, and Excel with a formula
In 1-2-3 for Windows, you can enter the following (rather awkward) formula in cell A2 to calculate the average of the last four filled-in cells in row 2:
@avg(@index(B2..BA2,@count(B2..BA2)-1,0), @index(B2..BA2,@count(B2..BA2)-2,0), @index(B2..BA2,@count(B2..BA2)-3,0), @index(B2..BA2,@count(B2..BA2)-4,0))
The @count function determines the number of weeks of data you've already entered. Working off that number, the first @index gets the last filled cell in the row; the second @index gets the next to last value, and so on. The @avg yields the average.
In Quattro Pro, you can simplify the procedure with an array formula:
@avg(@index(A2..BA2,@count(A2..BA2) -{1,2,3,4},0))
The array {1,2,3,4} causes the @index function to produce a series of values: the last four filled-in cells in row 2.
A similar formula would work in Excel, but that program's offset function lets you dispense with the array:
=average(offset(B2,0,count(B2:BA2)-4,1,4))
Here, the offset function finds a range beginning on row 2, four cells from the rightmost filled-in cell, one row high and four columns wide.
Note: You must enter data for every week. Missing values will throw off the count functions.
Q Is there a way in 1-2-3 to truncate a number without rounding it? For instance, how do I get 1-2-3 to change 14.156667 to 14.15?
- Vaughn Scala
A Actually there are several ways. Say the number you want to truncate to two decimal places is in cell A1. The following no-frills formula will work in any spreadsheet:
@int(A1*100)/100
The round-down function in 1-2-3 for Windows does the same thing:
@rounddown(A1,2)
Excel has a similar function:
=rounddown(A1,2)
Or you could use 1-2-3 for Windows' @trunc function:
@trunc(A1,2)
Q How do I selectively delete rows in Excel? We store data from instruments in our plant in an Excel worksheet, averaging values over 15-minute intervals. Occasionally the averaging process fails, and values that should be 0 or 1 come out as decimal fractions. I need a macro that will delete rows containing values that are greater than 0 but less than 1. Can you help?
- Jason Mohammed
A Sure. Let's say your list of instrument names begins in cell A1 of the worksheet, and the averages are in column B. The macro in will delete the rows in which column B values are greater than 0 and less than 1.232b.tif: Figure 2: Find and delete errant worksheet data with this Excel macro. The macro deletes rows containing values that are greater than 0 and less than 1
To set up the model, select Insert- Macro-Module and enter the code shown in Select Tools-Macro, select DeleteRows in the Macro Name/Reference list, and click the Options button. In the Macro Options dialogue box, click Shortcut Key, enter D in the Ctrl+ box, click OK, then click Close. To execute the macro, press <Ctrl>+D.
The macro is fairly self-explanatory. In step 2, note the use of the CurrentRegion command to identify the cells (beginning at cell A1) that contain data, then the Columns command to denote the second column within that region.
Q After importing text data from a mainframe report into my Excel 95 worksheet, I find that every other row is blank. How do I eliminate the blank rows from my worksheet?
- Sindey Wasch
A The macro in the preceding answer, which selectively deletes rows, will work, but since you just want to delete blank rows, Excel's AutoFilter is the way to go. Say your data looks like this:
First, select all the imported data: The best way is to select cell A1, then press
<Shift>+<Ctrl>+<End>
Excel extends the selection to the last filled cell at the lower right corner of the sheet.
Now, select Data-Filter-AutoFilter to turn filtering on. Click any one of the scroll buttons in row 1 (but first make sure there's no missing data in that column, or you'll delete the other columns' data), scroll to the bottom of the drop-down list, and select (Blanks), as in the figure at right ^^^ please alter this reference as required 234c.tif Now, select A2, press
<Shift>+<Ctrl>+<End>
to select all the empty rows, then select Edit-Delete Row and click OK. Finally, select Data-Filter-AutoFilter to turn off filtering, and that's all - no more blank rows.
Q I'm a meteorologist who uses Excel 5.0 to track the performance of my weather forecasters. We issue a variety of warnings and watches to the public, and we need to record how accurate we are in predicting the exact time of a storm - the difference between when we announced that the storm would happen and when it actually happened.
In my worksheet, one column contains the predicted event date and time, another column contains the actual event date and time, and formulas in a third column subtract the values in the second column from those in the first to calculate timing error.
My problem is that Excel won't display negative time values. Is there a way to get around this?
- John Galliano
A. Certainly. You might arrange your worksheet as shown in Figure 3. Be sure to enter the full date and time values in columns A and B and assign the columns the m/d/yy h:mm format.
Enter this formula in cell C2:
=if(A2-B2<0,"-","")&text(abs(A2-B2),"h:mm")
The abs() function calculates the absolute difference between the predicted and actual times (if the difference is negative, abs() yields a positive value). The text() function converts the difference to a text string and displays the result in the h:mm format, Excel's regular time format. The if() function tests whether the difference is negative. If so, it pastes a minus sign in front.
Since the timing errors are text strings, you can't use them in calculations. But if you want to summarise the error values, you can create array formulas that will work directly from the original values, using the same trick for displaying negative results. For example, the following formula averages the timing errors shown in Figure 3:
{=if(average(A2:A7-B2:B7)<0,"-","")&text(abs(average(A2:A7-B2:B7)),"h:mm")}234b.tif: Figure 3: Make time negative in Excel with a simple formula that combines an If() function with abs() and text() functions
Remember that you don't type the braces when you enter the formula. Excel adds them when you press <Ctrl>+<Shift>+<Enter> to enter the formula as an array.
By Richard Scoville