Using a function, add the numbers of the Hours Billed column together, but only

Question

Using a function, add the numbers of the Hours Billed column together, but only if the numbers are from the East region

Using a function, count the number of entries in the Total Hours column that are over 60

Using Data Validation, give the user an error message if he / she enters a date in column D that is before January 1, 2013.

Apply conditional formatting to column C that colors the background of each cell that starts with a W.

In J10, concatenate the following cells: B4, B5, C4, C5. Insert spaces where needed. In between B5 and C4, insert two pipes (this symbol |, which is on the backslash key).

*in excel, using functions and macros?*

Week of 4/6/2015 Region Company ID Work Order Date Hours Billed Misc Hours Total Hours East EC992 EA622 1/1/14 91.50 2.00 93.50 East HA827 EA693 2/22/14 35.00 9.00 44.00 East AI978 EA667 5/5/15 78.00 0.00 78.00 East AC508 EA627 6/23/15 15.25 11.50 26.75 East IK970 EA635 7/1/13 75.00 24.50 99.50 East OS878 EA687 9/23/14 80.00 9.50 89.50 East OW22 EA608 12/3/14 201.00 2.00 203.00 North KA985 NO471 4/12/13 87.00 2.50 89.50 North KA985 NO490 4/6/15 56.50 18.00 74.50 North HS887 NO475 9/7/13 72.00 1.00 73.00 North VE209 NO490 6/23/15 44.00 4.50 48.50 South HE988 SO253 9/23/14 59.75 7.00 66.75 South SH220 SO362 4/12/13 82.50 12.00 94.50 South AH890 SO399 1/1/14 24.00 12.00 36.00 South AE208 SO206 11/11/14 16.50 10.00 26.50 South HO885 SO399 12/6/13 124.00 5.50 129.50 West AE299 WE518 1/7/15 33.80 12.50 46.30 West AA927 WE668 9/3/13 118.00 0.00 118.00 West BT889 WE675 10/31/14 72.50 2.00 74.50 West HA758 WE502 1/17/15 28.50 10.50 39.00 West AH588 WE640 1/5/13 75.00 17.00 92.00 West EA580 WE577 9/12/13 95.00 8.50 103.50

Explanation / Answer

Excel does this by using formulas in cells. A formula performs calculations or other actions on the data in your worksheet. A formula always starts with an equal sign (=), which can be followed by numbers, math operators (such as a plus or minus sign), and functions, which can really expand the power of a formula.

For example, the following formula multiplies 2 by 3 and then adds 5 to that result to come up with the answer, 11.

=2*3+5

This next formula uses the PMT function to calculate a mortgage payment ($1,073.64), which is based on a 5 percent interest rate (5% divided by 12 months equals the monthly interest rate) over a 30-year period (360 months) for a $200,000 loan:

=PMT(0.05/12,360,200000)

Here are some additional examples of formulas that you can enter in a worksheet.

=A1+A2+A3    Adds the values in cells A1, A2, and A3.

=SQRT(A1)    Uses the SQRT function to return the square root of the value in A1.

=TODAY()    Returns the current date.

=UPPER("hello")     Converts the text "hello" to "HELLO" by using the UPPER worksheet function.

=IF(A1>0)    Tests the cell A1 to determine if it contains a value greater than 0.

Sub InsertTime()

    ActiveCell.FormulaR1C1 = "=NOW()"

    Selection.Copy

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,_

        SkipBlanks:=False, Transpose:=False

End Sub

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.