CELL REFERANCE IN EXCEL S.3

By AndTech-ug -

  A cell reference refers to the location of a cell in a worksheet and can be used in a formula so that Excel can find the values or data that you want the formula to calculate1.

For example, if you have data in cell A2 and you want to use that value in cell A1, you can use =A2 in cell A1. This will copy the value of A2 into A12. This is known as cell referencing in Excel2.

There are three types of cell references in Excel2:

  • Relative cell references: These are the most common and refer to cells relative to the position of the formula. If the position of the cell that contains the formula changes, the reference is changed
  • Absolute cell references: These refer to cells in a specific location. The reference to a cell doesn’t change even if the formula is moved or copied to another cell. Absolute references are marked by a dollar sign, for example $A$2.
  • Mixed cell references: These refer to a cell’s fixed column or row. Only the column or row element of the reference is fixed. For example, $A2 or A$2.

These references are crucial in Excel functions, formulas, charts, and commands.


ERROR MESSAGES IN EXCEL

Excel error messages are alerts that indicate something has gone wrong with the values or formulas in a cell. They are designed to help you identify and correct mistakes.

  Here are some common Excel error messages and what they mean12:

  1. ###### error: This error occurs when Excel is trying to display a number, but the column is too narrow to show the number in full. The most common solution is to make the column wider1.

  2. #CALC! error: This error may be referring to any of several calculation problems related to array formulas. For example, the FILTER function does not currently support empty arrays. If the condition defined within the include argument results in an empty array (i.e., does not exist), a #CALC! error will result1.

  3. #VALUE! error: Excel displays the #VALUE! error when it finds spaces, characters, or text in a formula where it is expecting a number. Excel requires formulas to contain numbers only and won’t respond to formulas associated with numbers2.

  4. #NAME? error: This error message appears if Excel can’t understand the name of the formula you’re trying to run, or if Excel can’t compute one or more values entered in the formula itself2.

  5. #DIV/0! error: This error occurs when a formula tries to divide a number by 0 or an empty cell.

  6. #N/A error: This error occurs when a value is not available to a function or formula.

  7. #NULL! error: This error occurs when you specify an intersection of two areas that do not intersect.

  8. #NUM! error: This error occurs when a formula or function contains invalid numeric values.

  9. #REF! error: This error occurs when a cell reference is not valid.

  10. #VALUE! error: This error occurs when the wrong type of argument or operand is used.

These error messages provide clues to identify mistakes in a formula or value, which helps to debug and fix errors. If you see an error message in Excel, consider it a welcome clue to identifying what went wrong and a first step to figuring out how to debug (or fix) that error1.

WORKING WITH ADVANCED FUNCTIONS IN EXCEL


Advanced excel functions include, 

  • Rank, 
  • if
  • count if
  • sumif
  • Vlookup
  • Hlookup


RANK FUNCTIONS

Is used to return the positions  of a number in a list by comparing its size in relation to others.

QN: find the position of each learner in the table below




For moses:

=AVERAGE(D2:G2)

=RANK(H2,$H$2:$H$16)

=IF(I2>40,"passed","failed") this is a single if. it is used to out put 2 comments only.

=IF(I2>=75,"A", IF(I2>=60,"B", IF(I2>=55,"C", IF(I2>=40,"D","F")))) this if is called nested IF. it is used for more than 2 comments eg during grading of learners






Comments

Post a Comment

Popular posts from this blog

CARE AND SAFETY OF ICT TOOLS S.1

By AndTech-ug -
Image
QN1. Suggest safety measures for ICT tools in your school? 2. Identify the ICT tools that cane be found in the computer lab and state their uses? Care and safety of ICTs    1. Use strong password to ensure only authorized users can access ICT tools.   2. Make sure all software and hardware are regularly updated .   3. Use Antivirus Softwares to detect and remeove malware   4. Encrypt sensitive data stored on ICT tools to prevent unauthorized access in case of theft .   5. Always perform regular data backups to prevent data loss in case of system failures.   6. Use monitoring tools to track ICT usage and detect any suspicious activities.   7. Secure ICT equipment and infrastructure in locked rooms to prevent unauthorized access or tampering.  8. Keep dust away using dust blower, mopping the ICT Room.  9. have your windows burglar proofed    COMPUTER LABORATORY   A computer lab is a designate...
Read more

Data Processing cycle S.1

By AndTech-ug -
Image
The ccycle from when data is input into a computer upto when it is out put as information is called Data processing cycle. The data processing cycle cycle undergoes 4 stages 1. input 2. precessing 3. output 4. storage Data is first collected and entered in a computer as an INPUT by input devices like mouse, keyboard. Data is then sent to the central precessing unit (CPU) for processing and the result is information. (OUTPUT)
Read more

SYSTEM AND DATA SECURITY

By AndTech-ug -
Image
Computer Security Computer security, also known as cybersecurity, involves protecting computer systems and networks from theft, damage, and unauthorized access. This includes safeguarding hardware, software, and data from cyber threats like hacking, viruses, and other malicious activities. Data Security Data security focuses specifically on protecting data from unauthorized access, corruption, or theft. It encompasses a wide range of practices and technologies to ensure the confidentiality, integrity, and availability of data. Key practices in data security include: ●      Data Encryption: Encoding data so that only authorized parties can read it. ●      Access Controls: Restricting access to data to authorized users. ●      Data Masking: Hiding actual data with modified content to protect sensitive information. ●      Data Backup and Recovery: Regularly saving copies of data to prevent l...
Read more