vba answers

Option Explicit

Function JoshFunc(r As Range)
    Dim temp As Double
    temp = WorksheetFunction.Sum(r)
    JoshFunc = Evaluate("sqrt(" & temp & ")")
End Function

Function JoshFunc2(r As Range)
    Dim theSum As Double
    Dim cell As Range
    For Each cell In r
        theSum = theSum + cell.Value
    Next cell
   
    JoshFunc2 = Evaluate("sqrt(" & theSum & ")")
    JoshFunc2 = theSum ^ 0.5
End Function

Sub Macro1()
    For Each cell In Selection
      If cell.Value >= 5 And cell.Value <= 50 Then
        With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
      End If
    Next cell
End Sub

Sample midterm 2

Date and Time:
1) Using just arithmetic, give me a date 5 years from now. (Pretend there are no leap years.)

2) Do the same using data and time functions.

Counting and Summing:

Generate a list of Salespeople and the number of Widgets they sold, and total revenue they brought in.
1) Count how many people sold between 8 and 23 widgets. Use summing functions. (e.g. sumif, countif)
2) Do the same using an array formula

Do the same based on length of salesperson's name (between 4 and five letters long)
1) using summing funcs
2) using array formulas

Lookup functions:
Look up for me Bob's revenue and #widgets sold, from same table

financial functions
If I borrow 1 million for 100 years, at 7% APR, compounded quarterly, with a balloon payment of half a million, what will be my payment?


If I borrow 1 million for 100 years, with a PMT of $1000 monthly, compounded quarterly, with a balloon payment of half a million, what will be my APR? What will be my annual effective rate?


VBA question:
write a UDF that takes in a range and returns the square root of the sum of all the elements in the range
make a macro that will color all values in a range between 5 and 50 the background color red.