Question

Type an MS EXCEL formula for each of the measures described below. The fifth root of the value in cell H5 The Final value given the principal, annual rate and number of years in cells A6, B6 and C6 respectively. calculate the value of two standard deviations above the mean, given the mean in 25 and the variance in Y5. The result of the increase if G8 is to be increased by the percentage in F8 The Principal, given the annual rate in A15, the number of investment years in A16 and the final value in A20. Here, interest is compounded every 6 months. The statistical range raised to the power of a half of values in the array of data ranging from A5 to C20. (vi) The square of a 1.5% depreciation of the mean of values in B5, C20 and N3:Ma (viii) An if statement(only one if statement) to display the text "SORRY" if the salary in D3 is more than the value in C1 or calculate a 10% bonus annount on that salary if it is less than the value in C1 by 150 kina or calculates 20% bonus on the salary for lower salaries The median of values in c5, d5 and E5:F6 divided by the mode of the same values, with the answer raised to the power of the value in G5. mitor (to the nearest minute)

How can I do this there is no data/Value provided?

Answer 1

Since cell addresses are given, we do not need actual values.

(i)

n^th root is calculated by raising the number to its (1/n)^th power

There, 5^th root of H5 is given by =H5^(1/5)

(ii)

If rate is given in % terms

The final value is given by =A6*B6*C6

if rate is given in absolute (numerical) terms

The final value is given by =(A6*B6*C6)/100

(iii)

Standard deviation is the square root of variance.

The required value is given by =Z5+2*(Y5^(1/2)) or =Z5+(2*SQRT(Y5))

(iv)

Increase in % terms is simply calculated by Final Value = Initial Value*(1+Percentage Increase)

The required value is given by =G8*(1+F8)

(v)

Final Value = Principal * (1+Annual Rate/2)^^{(2*Investment Years)}

i.e. Principal = Final Value/ (1+Annual Rate/2)^^{(2*Investment Years)}

This means, Principal = A20/(1+A15/2)^(2*A16)

(vi)

Statistical range is given by the difference of the highest number in the data set and the lowest number in the data set.

Statistical range = max(A5:C20)-min(A5:C20)

Half of values = sum(A5:C20)/2

The required value is given by =(MAX(A5:C20)-MIN(A5:C20))^(SUM(A5:C20)/2)

(vii)

Required mean is given by average(B5,C20,N3:M4)

The required value is =(AVERAGE(B5,C20,N3:M4)*(1-1.5%))^2

(viii)

Assuming the same currency units throughout.

=if(D3>C1,"SORRY",if(C1-D3<151,10%*D3,20%*D3))

Note: Nested if condition is still just one if condition.

(ix)

There is an inbuilt median function that can be used.

=MEDIAN(C5,D5,E5:F6)^G5