HOW TO
FIND POSITION OF DATA [SCORES] IN EXCEL
Procedures and concepts to consider:
I.FIND THE AVERAGE OF SCORES
- Have a data into cells [that are to be average calculated] Example [13 + 25 + 14] ÷ 3
=AVERAGE(13;25;14)
- Identify the column name and row name
Example
Columns
[B, C and D]
and Row
[2]
…....... Horizontal calculation
OR
Column
[B]
and Rows
[2,3,4 and 5]
…........ Vertical calculation
- Identify the range of data into cells [that are to be average calculated]
Example
B2 to D2 (B2:D2)
- Syntax: '' = AVERAGE(B2:D2) ''
- Syntax: '' = AVERAGE(B2:B5) ''
II. FIND THE POSITION OF DATA
[By Ranking from highest to smallest]
- Identify the range of data [to be ranked] Example E2:E6 [In vertical]
OR
B7:D7
[In horizontal]
- In writing syntax, we use one or more Dollar signs [$] in the cell reference to stop Excel from automatically adjusting the cell reference. These are called Absolute Cell References.
EXAMPLE
- ''E2'' means that, the "E" will increment one letter for every cell that I move over to the right and will decrement one letter for every cell that I move over to the left. Then, the "2" will increment by one for every cell that I move down and will increment by one for every cell that I move down.
- $E$2 This is an absolute cell reference. Means that, If we copy a formula with that cell reference, the cell reference will NOT change AT ALL.
- $E2 means that, The "E" will stay the same when we copy the cell, but the "2" will change.
- E$2 means that, The "E" will change when we copy the cell, but the "2" will stay the same.
Therefore;
- Syntax: '' = RANK(E2;$E$2:$E$6)+COUNTIF($E$2:B2;B2)-1 '' ….....[In vertical]
- Syntax: '' = RANK(B7;$B$7:$D$7)+COUNTIF($B$7:B7;B7)-1 '' ….....[In horizontal]
NOTE:
In writing formula, use semi-colon [;]
if you use OpenOffice
otherwise use comma only [,]
if you use Microsoft
office
OTHER EXCEL FUNCTIONS [SYNTANX]
tupe mavitu ya hatareee
ReplyDeleteOkay ....... thanks, keep on visiting this blog.
Delete👍
ReplyDeleteKazi nzuri sana,naomba formula ya kupanga division
ReplyDelete