HOW TO ANSWER THE PROJECT WORK QUESTIONS
Open a new excel file and name the sheet one (Question 1) [The name of the sheet can be all
caps if you want]
QUESTION 1
Type the headings and all the values as indicated in the question
NB: For the sum and average values; you can type it as it is or use the formula to
calculate for you but, I suggest you use the formula to calculate for you
=SUM(C2: E2), =AVERAGE(C2: E2) , and so on for each cell.
You can then type the maximum mark, minimum mark and the others u asked to
calculate for maybe two cells behind the last cell in column A or the Student No Column.
Type in the function name for each of the marks u are questioned on under the maths,
chemistry and physics column , using the formula’s below
➤ Maximum
=MAX(C2:C6) → for Maths
=MAX(D2:D6) → for Physics
=MAX(E2:E6) → for Chemistry
➤ Minimum
=MIN(C2:C6)
=MIN(D2:D6)
=MIN(E2:E6)
➤ Mean (Average)
=AVERAGE(C2:C6)
=AVERAGE(D2:D6)
=AVERAGE(E2:E6)
➤ Median
=MEDIAN(C2:C6)
=MEDIAN(D2:D6)
=MEDIAN(E2:E6)
➤ Standard Deviation
=STDEV.S(C2:C6)
=STDEV.S(D2:D6)
=STDEV.S(E2:E6)
➤ Variance
=VAR.S(C2:C6)
=VAR.S(D2:D6)
=VAR.S(E2:E6)
You can one by one, but if you want to save time, do just one and click on the bottom of the cell,
and pull to the right, an autofill will be done for you.
So u would get something like this
QUESTION 2:
This question is about quadratic equations and we know that the numbers in a quadratic equation
are represented by a, b and c. and depending on their values we can calculate discriminant ,
check whether a an equation has some features such as whether has x1 and x2 don't exist, x2
doesn't exist, Imaginary roots exist? , Two roots exist? Or not. So basically we will input
random a,b,c numbers, 20 each to check for the headings indicated in the question.
So we will start our work from sheet 2, which I said earlier in question 1 that we should name it
Question 2.
So we start with the headings which occupy column A to H
a b c discriminant x1 and x2 x2 Imaginary
don't doesn't roots Two roots
exist exist exist? exist?
-1 -3 5 29 FALSE FALSE FALSE TRUE
0 -2 -3 4 FALSE TRUE FALSE FALSE
1 -1 -2 9 FALSE FALSE FALSE TRUE
2 0 -1 8 FALSE FALSE FALSE TRUE
3 1 0 1 FALSE FALSE FALSE TRUE
First randomly input twenty numbers each into column a, b and c. [ some of the numbers
can be negative, some can be zero, it does not matter because the lecturer did not give
restrictions to numbers in the questions]
Discriminant in theoretical calculations is calculated as b² - 4ac, so from using the above
table as an example, we input the formula below using cell names =B2^2 - 4*A2*C2
[ because cell B2 has the number b which is then squared , after a minus sign is brought
and 4 is multiplied by A2 and C2, where A2 has the a value and C2 has the c value]
To make things easier for you when you done with the first cell in discriminant column,
select it and click on the + signs that appears and pull down to the last cell in the column ,
this will autofill all the discriminant values in the cell
Quadratic equations has two roots, x1 and x2 , so for the x1 and x2 don't exist column,
=AND(A2=0, B2=0), this will
input the formula below into the first cell beneath it,
calculate it like we did in logic gates , so if both A2 and B2 are zero, then it
gives TRUE as the outcome but if even one is not equal to zero, you get a
FALSE statement
For the x2 doesn't exist column; use the formula;
=AND(A2=0, B2< >0)
< > in Excel is "not equal to" operator.
So:
- If B2 contains something other than 0 (like 3, -1, 7...), the result is TRUE.
- If B2 equals 0, the result is FALSE.
And we know that for the AND logic both statements in the bracket must be obeyed for
the final outcome to be TRUE if the opposite then FALSE.
When u done autofill or just change the A2 n B2 to suite the corresponding cell number
for each cell beneath that column
For the Imaginary roots exist? Column;
This column basically means that if D column, that is the discriminant column is less
than zero then true there is an imaginary root, but if it is equal to zero or greater than
zero, then false, imaginary root does not exist
Use this formula in the first cell;
=IF(D2<0, TRUE, FALSE)
And then autofill the rest of the cells of the Imaginary roots exist? Column
OR u can change the D2, to D3, D4 using the same formula as u go down that particular
column.
FOR THE Two roots exist? COLUMN
We use the formula; =IF(AND(A2<>0, D2>=0), TRUE, FALSE)
THIS means that if A column is not equal to zero and also the discriminant is equal
or greater than zero, then its TRUE (two roots exist), IF NOT THEN FALSE
When u done filling, u can autofill the rest of the column or change the cell number to match that
corresponding column
QUESTION 3
For this question it also deals with logic circuit, since we know that there are 3 inputs,
basically, they would be 8 possible combinations using the formula 2 exponent n, where
n is 3, in this case.
I suggest u write all the possible inputs down and write out the output for for each
possible input in the alarms following the instructions.
You can just type in the results in excel and go, but then you would not get all the
marks, the lecturer is looking for the formulas you used, which she would check in the
formula bar, so if u just typed numbers or used a wrong formula, then some marks will
be deducted. For me I tried out many formulas, so if those formulas do not give me my
desired output, I would immediately know am wrong. So after much changes these are
the formulas to get your desired output.
Sensor A Sensor B Sensor C Alarm 1 Alarm 2 Alarm 3
0 0 0 0 0 0
1 1 0 1 1 1
0 1 1 1 1 1
1 0 1 1 1 1
1 0 0 0 1 0
0 0 1 1 1 0
0 1 0 1 0 0
1 1 1 1 1 1
Ignore the colored headings
For the sensor A to C column; you will just type out all the possible combinations;
For Alarm 1 column this is the formula; =IF(OR(C2=1, B2=1), "1", "0")
U can then autofill if u want, or change the cell name to C3, B3 C4, B4 and so on
For Alarm 2 column this is the formula; IF(OR(C2=1, A2=1), "1", "0")
U can then autofill if u want, or change the cell name to C3, A3 C4, A4 and so on
For the Alarm 3 column, this is the formula; =IF((A2+B2+C2)>=2, 1, 0)
U can then autofill or change the cell name to A3+B3+C3, A4+B4+C4, and so on
I suggest you try out this question in different ways and try to get your own formula as
maybe in the IA, the same might come
QUESTION 4:
Type in the data set provided and use the formulas;
➤ Average
=AVERAGE(range)
➤ Standard Deviation
=STDEV.S(range)
➤ Variance
=VAR.S(range)
The range depends on the cell you start your data from
d. Polynomial Regression Chart
1. Select data for X and Y.
2. Insert a Scatter Plot.
3. Add Trendline, choose Polynomial, and set Order 2 or 3.
4. Check “ Display Equation on chart” and “ Display R-squared value”.