OpenOffice Calc Formulas How-To || Calc Formula List with Example | Calc Formula pdf file

Rajesh Kumar
BCC Course Exam

LibreOffice Calc सूत्र स्प्रेडशीट प्रबंधन के लिए मौलिक उपकरण हैं। बुनियादी फ़ंक्शंस जैसे SUM और AVERAGE उपयोगकर्ताओं को सरल गणितीय संचालन और सांख्यिकीय विश्लेषण करने की अनुमति देते हैं, जबकि अधिक उन्नत फ़ंक्शंस जैसे VLOOKUP और IF जटिल डेटा क्वेरी और शर्तीय तर्क को संभालने में मदद करते हैं। पाठ प्रसंस्करण के लिए, CONCATENATE और LEFT जैसे फ़ंक्शंस अनमोल हैं, जबकि तिथि और समय के फ़ंक्शंस जैसे NOW और DATE समय संबंधी डेटा प्रबंधित करने में मदद करते हैं। इन फ़ंक्शंस को मास्टर करना बदलते डेटा आवश्यकताओं के लिए गतिशील स्प्रेडशीट्स बनाने के लिए महत्वपूर्ण है।

Calc Formulas And Functions pdf fileDownload

SUM (संपूर्ण योग):

  • Description: Adds up a range of numbers.
  • Example: =SUM(A1:A5)
  • Explanation: A1 से A5 तक के सेल्स में जो भी नंबर हैं, उनका योग करता है। उदाहरण के लिए, अगर A1 में 10, A2 में 20, A3 में 30, A4 में 40, और A5 में 50 है, तो =SUM(A1:A5) 150 लौटाएगा।

AVERAGE (औसत):

  • Description: Calculates the average of a range of numbers.
  • Example: =AVERAGE(B1:B5)
  • Explanation: B1 से B5 तक के नंबरों का औसत निकालता है। अगर B1 में 5, B2 में 15, B3 में 25, B4 में 35, और B5 में 45 है, तो =AVERAGE(B1:B5) 25 लौटाएगा।

COUNT (गिनती):

  • Description: Counts the number of cells containing numbers.
  • Example: =COUNT(C1:C10)
  • Explanation: C1 से C10 तक के सेल्स में जो भी नंबर हैं, उनकी गिनती करता है। यदि C1 में 10, C2 में खाली, C3 में 20, और C4 में 30 है, तो =COUNT(C1:C10) 3 लौटाएगा।

IF (यदि):

  • Description: Performs a logical test and returns one value if true and another if false.
  • Example: =IF(D1>50, "Pass", "Fail")
  • Explanation: D1 की वैल्यू 50 से अधिक होने पर “Pass” और अन्यथा “Fail” लौटाता है। यदि D1 में 60 है, तो =IF(D1>50, "Pass", "Fail") “Pass” लौटाएगा।

VLOOKUP (वर्टिकल लुकअप):

  • Description: Searches for a value in the first column of a range and returns a value in the same row from a specified column.
  • Example: =VLOOKUP(E2, A2:B10, 2, FALSE)
  • Explanation: E2 में मान की खोज करता है और A2रेंज में दूसरी कॉलम से संबंधित मूल्य लौटाता है। यदि E2 में “John” है और A2में John का स्कोर 85 है, तो =VLOOKUP(E2, A2:B10, 2, FALSE) 85 लौटाएगा।

HLOOKUP (हॉरिजेंटल लुकअप):

  • Description: Searches for a value in the top row of a range and returns a value in the same column from a specified row.
  • Example: =HLOOKUP(F1, A1:D4, 3, FALSE)
  • Explanation: F1 में मान की खोज करता है और A1रेंज में तीसरी पंक्ति से संबंधित मूल्य लौटाता है। अगर F1 में “Sales” है और तीसरी पंक्ति में Sales का आंकड़ा 1200 है, तो =HLOOKUP(F1, A1:D4, 3, FALSE) 1200 लौटाएगा।

CONCATENATE (संयोजन):

  • Description: Joins together two or more text strings.
  • Example: =CONCATENATE(G1, " ", H1)
  • Explanation: G1 और H1 की सामग्री को जोड़ता है। यदि G1 में “Hello” और H1 में “World” है, तो =CONCATENATE(G1, " ", H1) “Hello World” लौटाएगा।

LEFT (बाएँ):

  • Description: Returns a specified number of characters from the start of a text string.
  • Example: =LEFT(I1, 3)
  • Explanation: I1 में पहले 3 अक्षर लौटाता है। यदि I1 में “Computer” है, तो =LEFT(I1, 3) “Com” लौटाएगा।

RIGHT (दाएँ):

  • Description: Returns a specified number of characters from the end of a text string.
  • Example: =RIGHT(J1, 4)
  • Explanation: J1 के अंतिम 4 अक्षर लौटाता है। यदि J1 में “Education” है, तो =RIGHT(J1, 4) “tion” लौटाएगा।

MID (मध्य):

  • Description: Returns a specific number of characters from a text string starting at a specified position.
  • Example: =MID(K1, 2, 5)
  • Explanation: K1 में दूसरी स्थिति से शुरू होकर 5 अक्षर लौटाता है। यदि K1 में “Programming” है, तो =MID(K1, 2, 5) “rogra” लौटाएगा।

TRIM (छाँटें):

  • Description: Removes extra spaces from a text string.
  • Example: =TRIM(L1)
  • Explanation: L1 से अतिरिक्त खाली स्थान हटा देता है। यदि L1 में ” Hello World ” है, तो =TRIM(L1) “Hello World” लौटाएगा।

UPPER (उच्चारण):

  • Description: Converts text to uppercase.
  • Example: =UPPER(M1)
  • Explanation: M1 की सामग्री को सभी बड़े अक्षरों में बदल देता है। अगर M1 में “welcome” है, तो =UPPER(M1) “WELCOME” लौटाएगा।

LOWER (निचे):

  • Description: Converts text to lowercase.
  • Example: =LOWER(N1)
  • Explanation: N1 की सामग्री को सभी छोटे अक्षरों में बदल देता है। अगर N1 में “WELCOME” है, तो =LOWER(N1) “welcome” लौटाएगा।

PROPER (उचित):

  • Description: Capitalizes the first letter of each word in a text string.
  • Example: =PROPER(O1)
  • Explanation: O1 की प्रत्येक शब्द के पहले अक्षर को बड़ा कर देता है। यदि O1 में “hello world” है, तो =PROPER(O1) “Hello World” लौटाएगा।

LEN (लंबाई):

  • Description: Returns the number of characters in a text string.
  • Example: =LEN(P1)
  • Explanation: P1 में कुल अक्षरों की गिनती करता है। यदि P1 में “LibreOffice” है, तो =LEN(P1) 11 लौटाएगा।

FIND (खोजें):

  • Description: Finds the position of a substring within a text string.
  • Example: =FIND("text", Q1)
  • Explanation: Q1 में “text” की स्थिति खोजता है। यदि Q1 में “This is a text example” है, तो =FIND("text", Q1) 11 लौटाएगा।

SEARCH (खोजें):

  • Description: Finds the position of a substring within a text string (case-insensitive).
  • Example: =SEARCH("example", R1)
  • Explanation: R1 में “example” की स्थिति खोजता है, केस की परवाह किए बिना। अगर R1 में “Another Example” है, तो =SEARCH("example", R1) 9 लौटाएगा।

REPLACE (बदलें):

  • Description: Replaces part of a text string with another text string.
  • Example: =REPLACE(S1, 1, 5, "NewText")
  • Explanation: S1 की पहली 5 अक्षरों को “NewText” से बदल देता है। यदि S1 में “HelloWorld” है, तो =REPLACE(S1, 1, 5, "NewText") “NewTextWorld” लौटाएगा।

SUBSTITUTE (स्थानापन्न):

  • Description: Replaces occurrences of a specified text string with another text string.
  • Example: =SUBSTITUTE(T1, "old", "new")
  • Explanation: T1 में “old” शब्द को “new” से बदल देता है। यदि T1 में “old data” है, तो =SUBSTITUTE(T1, "old", "new") “new data” लौटाएगा।

ROUND (गोल):

  • Description: Rounds a number to a specified number of digits.
  • Example: =ROUND(U1, 2)
  • Explanation: U1 को दो दशमलव स्थानों तक गोल करता है। यदि U1 में 3.14159 है, तो =ROUND(U1, 2) 3.14 लौटाएगा।

NOW (अब):

  • Description: Returns the current date and time.
  • Example: =NOW()
  • Explanation: वर्तमान दिनांक और समय लौटाता है।

TODAY (आज):

  • Description: Returns the current date.
  • Example: =TODAY()
  • Explanation: वर्तमान तिथि लौटाता है।

DATE (तिथि):

  • Description: Returns the serial number of a specific date.
  • Example: =DATE(2024, 8, 11)
  • Explanation: 11 अगस्त 2024 की तिथि का सीरियल नंबर लौटाता है।

TIME (समय):

  • Description: Returns the serial number of a specific time.
  • Example: =TIME(14, 30, 0)
  • Explanation: 14:30:00 (2:30 PM) का समय लौटाता है।

DAYS (दिन):

  • Description: Returns the number of days between two dates.
  • Example: =DAYS(V1, W1)
  • Explanation: V1 और W1 के बीच के दिनों की संख्या लौटाता है। यदि V1 में 01-Aug-2024 और W1 में 11-Aug-2024 है, तो =DAYS(V1, W1) 10 लौटाएगा।

YEAR (वर्ष):

  • Description: Returns the year of a specified date.
  • Example: =YEAR(X1)
  • Explanation: X1 की तिथि से वर्ष लौटाता है। अगर X1 में 15-Dec-2023 है, तो =YEAR(X1) 2023 लौटाएगा।

MONTH (माह):

  • Description: Returns the month of a specified date.
  • Example: =MONTH(Y1)
  • Explanation: Y1 की तिथि से माह लौटाता है। अगर Y1 में 11-Aug-2024 है, तो =MONTH(Y1) 8 लौटाएगा।

DAY (दिन):

  • Description: Returns the day of a specified date.
  • Example: =DAY(Z1)
  • Explanation: Z1 की तिथि से दिन लौटाता है। यदि Z1 में 21-Jun-2024 है, तो =DAY(Z1) 21 लौटाएगा।

HOUR (घंटा):

  • Description: Returns the hour of a specified time.
  • Example: =HOUR(AA1)
  • Explanation: AA1 में समय से घंटा लौटाता है। यदि AA1 में 15:45:00 है, तो =HOUR(AA1) 15 लौटाएगा।

MINUTE (मिनट):

  • Description: Returns the minute of a specified time.
  • Example: =MINUTE(AB1)
  • Explanation: AB1 में समय से मिनट लौटाता है। यदि AB1 में 10:30:00 है, तो =MINUTE(AB1) 30 लौटाएगा।

SECOND (सेकंड):

  • Description: Returns the second of a specified time.
  • Example: =SECOND(AC1)
  • Explanation: AC1 में समय से सेकंड लौटाता है। यदि AC1 में 12:25:45 है, तो =SECOND(AC1) 45 लौटाएगा।

POWER (पावर):

  • Description: Returns the result of a number raised to a power.
  • Example: =POWER(2, 3)
  • Explanation: 2 को 3 की शक्ति तक उठाता है, जो कि 8 होगा।

SQRT (वर्गमूल):

  • Description: Returns the square root of a number.
  • Example: =SQRT(16)
  • Explanation: 16 का वर्गमूल लौटाता है, जो कि 4 होगा।

ABS (पूर्णांक):

  • Description: Returns the absolute value of a number.
  • Example: =ABS(-15)
  • Explanation: -15 का पूर्णांक मूल्य लौटाता है, जो कि 15 होगा।

RAND (रैंडम):

  • Description: Returns a random number between 0 and 1.
  • Example: =RAND()
  • Explanation: 0 और 1 के बीच एक यादृच्छिक संख्या लौटाता है।

RANDBETWEEN (रैंडम बिच):

  • Description: Returns a random number between the specified numbers.
  • Example: =RANDBETWEEN(1, 100)
  • Explanation: 1 और 100 के बीच एक यादृच्छिक संख्या लौटाता है।

EFFECT (प्रभाव):

  • Description: Calculates the effective annual interest rate.
  • Example: =EFFECT(0.08, 12)
  • Explanation: 8% वार्षिक ब्याज दर का प्रभावी वार्षिक ब्याज दर लौटाता है, जब ब्याज की अवधि प्रति वर्ष 12 हो।

INTEREST (ब्याज):

  • Description: Calculates the interest on a loan or investment.
  • Example: =INTEREST(10000, 0.05, 12)
  • Explanation: 10000 की राशि पर 5% ब्याज पर 12 महीने का ब्याज लौटाता है।

NOMINAL (नाममात्र):

  • Description: Returns the nominal annual interest rate.
  • Example: =NOMINAL(0.05, 12)
  • Explanation: 5% मासिक ब्याज दर का नाममात्र वार्षिक ब्याज दर लौटाता है।

RATE (दर):

  • Description: Calculates the interest rate per period of an annuity.
  • Example: =RATE(12, -100, 1200)
  • Explanation: 12 अवधि में 100 की मासिक भुगतान के लिए ब्याज दर लौटाता है।

PMT (किस्त):

  • Description: Calculates the payment for a loan based on constant payments and a constant interest rate.
  • Example: =PMT(0.05/12, 12, 1000)
  • Explanation: 12 महीने के लिए 1000 की राशि पर 5% ब्याज दर पर मासिक किस्त लौटाता है।

IPMT (ब्याज भुगतान):

  • Description: Calculates the interest payment for a given period.
  • Example: =IPMT(0.05/12, 1, 12, 1000)
  • Explanation: पहले महीने के लिए 1000 की राशि पर 5% ब्याज दर का ब्याज भुगतान लौटाता है।

CUMIPMT (संचयी ब्याज भुगतान):

  • Description: Calculates the cumulative interest paid on a loan between two periods.
  • Example: =CUMIPMT(0.05/12, 12, 1000, 1, 12, 0)
  • Explanation: 1 से 12 अवधि के बीच 1000 की राशि पर 5% ब्याज दर का संचयी ब्याज भुगतान लौटाता है।

CUMPRINC (संचयी प्रिंसिपल):

  • Description: Calculates the cumulative principal paid on a loan between two periods.
  • Example: =CUMPRINC(0.05/12, 12, 1000, 1, 12, 0)
  • Explanation: 1 से 12 अवधि के बीच 1000 की राशि पर 5% ब्याज दर का संचयी प्रिंसिपल भुगतान लौटाता है।

PV (वर्तमान मूल्य):

  • Description: Calculates the present value of an investment.
  • Example: =PV(0.05, 12, -100)
  • Explanation: 12 अवधि के लिए 5% ब्याज दर पर 100 की वर्तमान मूल्य लौटाता है।

FV (भविष्य मूल्य):

  • Description: Calculates the future value of an investment based on periodic, constant payments and a constant interest rate.
  • Example: =FV(0.05, 12, -100)
  • Explanation: 12 अवधि के लिए 5% ब्याज दर पर 100 की भविष्य मूल्य लौटाता है।

YEARFRAC (वर्षीय अंश):

  • Description: Returns the year fraction between two dates.
  • Example: =YEARFRAC(A1, B1)
  • Explanation: A1 और B1 के बीच के वर्षों का अंश लौटाता है।

NPV (शुद्ध वर्तमान मूल्य):

  • Description: Calculates the net present value of an investment based on a series of cash flows and a discount rate.
  • Example: =NPV(0.05, C1:C5)
  • Explanation: C1 से C5 तक की नकद प्रवाह और 5% छूट दर पर शुद्ध वर्तमान मूल्य लौटाता है।

IRR (आंतरिक लाभदायक दर):

  • Description: Calculates the internal rate of return for a series of cash flows.
  • Example: =IRR(D1:D5)
  • Explanation: D1 से D5 तक की नकद प्रवाह के लिए आंतरिक लाभदायक दर लौटाता है।

HYPERLINK (हाइपरलिंक):

  • Description: Creates a hyperlink to a specified location.
  • Example: =HYPERLINK("http://www.example.com", "Click Here")
  • Explanation: “Click Here” टेक्स्ट को “http://www.example.com” यूआरएल से जोड़ता है।

1. SUM (संपूर्ण योग)

  • Question:=SUM(A1:A10) formula returns:
    • a) The average of A1 to A10
    • b) The total of A1 to A10
    • c) The number of cells from A1 to A10
    • d) The maximum value in A1 to A10

2. AVERAGE (औसत)

  • Question: What does =AVERAGE(B1:B10) calculate?
    • a) The sum of B1 to B10
    • b) The median of B1 to B10
    • c) The average of B1 to B10
    • d) The product of B1 to B10

3. COUNT (गिनती)

  • Question: The formula =COUNT(C1:C10) returns:
    • a) The sum of cells in C1 to C10
    • b) The number of numeric entries in C1 to C10
    • c) The count of text entries in C1 to C10
    • d) The average value in C1 to C10

4. IF (यदि)

  • Question: The formula =IF(D1>50, "Pass", "Fail") evaluates:
    • a) The value in D1 against a threshold of 50 and returns “Pass” if greater
    • b) The sum of values in D1 and 50
    • c) The average of values in D1 and 50
    • d) The product of D1 and 50

5. VLOOKUP (वर्टिकल लुकअप)

  • Question: What does =VLOOKUP(E2, A2:B10, 2, FALSE) do?
    • a) Looks for E2 in the second column and returns the corresponding value
    • b) Looks for E2 in the first column and returns the corresponding value from the second column
    • c) Returns the sum of values in A2
    • d) Returns the average of values in A2

6. HLOOKUP (हॉरिजेंटल लुकअप)

  • Question:=HLOOKUP(F1, A1:D4, 3, FALSE) is used to:
    • a) Search for F1 in the top row and return the value from the third row
    • b) Search for F1 in the leftmost column and return the value from the third column
    • c) Return the sum of the third row values
    • d) Return the average of the third row values

7. CONCATENATE (संयोजन)

  • Question: The function =CONCATENATE(G1, " ", H1) is used for:
    • a) Adding G1 and H1
    • b) Combining G1 and H1 with a space in between
    • c) Finding the length of G1 and H1
    • d) Replacing text in G1 with H1

8. LEFT (बाएँ)

  • Question:=LEFT(I1, 4) extracts:
    • a) The last 4 characters of I1
    • b) The first 4 characters of I1
    • c) The middle 4 characters of I1
    • d) The total number of characters in I1

9. RIGHT (दाएँ)

  • Question: What does =RIGHT(J1, 3) return?
    • a) The first 3 characters of J1
    • b) The last 3 characters of J1
    • c) The middle 3 characters of J1
    • d) The length of J1

10. MID (मध्य)

  • Question:=MID(K1, 2, 5) extracts:
    • a) 5 characters from the beginning of K1
    • b) 5 characters starting from the 2nd character in K1
    • c) The first 5 characters of K1
    • d) The 2nd character in K1

11. TRIM (छाँटें)

  • Question: The formula =TRIM(L1) is used to:
    • a) Remove extra spaces from L1
    • b) Add spaces to L1
    • c) Find the length of L1
    • d) Change the case of L1

12. UPPER (उच्चारण)

  • Question:=UPPER(M1) converts:
    • a) Text in M1 to lowercase
    • b) Text in M1 to uppercase
    • c) Numbers in M1 to text
    • d) Dates in M1 to text

13. LOWER (निचे)

  • Question: What does =LOWER(N1) do?
    • a) Converts text in N1 to uppercase
    • b) Converts text in N1 to lowercase
    • c) Calculates the length of N1
    • d) Finds the position of N1

14. PROPER (उचित)

  • Question:=PROPER(O1) converts:
    • a) Text in O1 to all uppercase
    • b) Text in O1 to all lowercase
    • c) The first letter of each word in O1 to uppercase
    • d) The last letter of each word in O1 to uppercase

15. LEN (लंबाई)

  • Question:=LEN(P1) returns:
    • a) The number of numeric entries in P1
    • b) The length of the text in P1
    • c) The first character in P1
    • d) The sum of characters in P1

16. FIND (खोजें)

  • Question:=FIND("text", Q1):
    • a) Finds the position of “text” in Q1
    • b) Replaces “text” with another string in Q1
    • c) Counts occurrences of “text” in Q1
    • d) Calculates the length of “text” in Q1

17. SEARCH (खोजें)

  • Question: What does =SEARCH("example", R1) do?
    • a) Finds the position of “example” in R1 (case-sensitive)
    • b) Finds the position of “example” in R1 (case-insensitive)
    • c) Replaces “example” in R1
    • d) Counts occurrences of “example” in R1

18. REPLACE (बदलें)

  • Question:=REPLACE(S1, 1, 5, "NewText"):
    • a) Replaces the first 5 characters in S1 with “NewText”
    • b) Adds “NewText” to the end of S1
    • c) Finds “NewText” in S1 and replaces it
    • d) Extracts “NewText” from S1

19. SUBSTITUTE (स्थानापन्न)

  • Question:=SUBSTITUTE(T1, "old", "new"):
    • a) Replaces all instances of “old” with “new” in T1
    • b) Adds “new” before “old” in T1
    • c) Finds “old” and replaces it with the length of “new” in T1
    • d) Converts “old” to “new” and returns the length

20. ROUND (गोल)

  • Question:=ROUND(U1, 2):
    • a) Rounds the number in U1 to the nearest integer
    • b) Rounds the number in U1 to 2 decimal places
    • c) Rounds the number in U1 to the nearest hundred
    • d) Rounds the number in U1 to the nearest thousand

21. NOW (अब)

  • Question:=NOW():
    • a) Returns the current date
    • b) Returns the current date and time
    • c) Returns the time only
    • d) Returns the current date of next month

22. TODAY (आज)

  • Question:=TODAY():
    • a) Returns the current time
    • b) Returns the current date
    • c) Returns the date and time of last entry
    • d) Returns the date of the last day of the month

23. DATE (तिथि)

  • Question:=DATE(2024, 8, 11):
    • a) Returns the serial number for 11 August 2024
    • b) Returns the date format of 11 August 2024
    • c) Returns 11 August of the current year
    • d) Returns the day of the week for 11 August 2024

24. TIME (समय)

  • Question:=TIME(14, 30, 0):
    • a) Returns the serial number for 14:30:00
    • b) Returns the date for 14:30:00
    • c) Returns the time in hours only
    • d) Returns the time in minutes only

25. DAYS (दिन)

  • Question:=DAYS(V1, W1):
    • a) Returns the number of days between V1 and W1
    • b) Returns the day of the week for V1 and W1
    • c) Returns the day difference between two dates
    • d) Returns the month difference between two dates

26. YEAR (वर्ष)

  • Question:=YEAR(X1):
    • a) Returns the month of the date in X1
    • b) Returns the day of the date in X1
    • c) Returns the year of the date in X1
    • d) Returns the time of the date in X1

27. MONTH (माह)

  • Question:=MONTH(Y1):
    • a) Returns the day of the date in Y1
    • b) Returns the month of the date in Y1
    • c) Returns the year of the date in Y1
    • d) Returns the time of the date in Y1

28. DAY (दिन)

  • Question:=DAY(Z1):
    • a) Returns the month of the date in Z1
    • b) Returns the day of the date in Z1
    • c) Returns the year of the date in Z1
    • d) Returns the time of the date in Z1

29. HOUR (घंटा)

  • Question:=HOUR(AA1):
    • a) Returns the minute of the time in AA1
    • b) Returns the hour of the time in AA1
    • c) Returns the second of the time in AA1
    • d) Returns the day of the time in AA1

30. MINUTE (मिनट)

  • Question:=MINUTE(AB1):
    • a) Returns the hour of the time in AB1
    • b) Returns the second of the time in AB1
    • c) Returns the minute of the time in AB1
    • d) Returns the day of the time in AB1

31. SECOND (सेकंड)

  • Question:=SECOND(AC1):
    • a) Returns the minute of the time in AC1
    • b) Returns the second of the time in AC1
    • c) Returns the hour of the time in AC1
    • d) Returns the day of the time in AC1

32. POWER (पावर)

  • Question:=POWER(2, 3):
    • a) Returns 2 raised to the power of 3
    • b) Returns the square root of 2
    • c) Returns 2 multiplied by 3
    • d) Returns the cube root of 2

33. SQRT (वर्गमूल)

  • Question:=SQRT(16):
    • a) Returns the square of 16
    • b) Returns the square root of 16
    • c) Returns the cube of 16
    • d) Returns the cube root of 16

34. ABS (पूर्णांक)

  • Question:=ABS(-15):
    • a) Returns the positive value of -15
    • b) Returns the negative value of -15
    • c) Returns the absolute value of -15
    • d) Returns the sum of -15 and its absolute value

35. RAND (रैंडम)

  • Question:=RAND():
    • a) Returns a random integer between 0 and 1
    • b) Returns a random number between 0 and 1
    • c) Returns a fixed random number
    • d) Returns the average of random numbers

36. RANDBETWEEN (रैंडम बिच)

  • Question:=RANDBETWEEN(1, 100):
    • a) Returns a random number between 1 and 100
    • b) Returns a random number between 0 and 1
    • c) Returns a random number greater than 100
    • d) Returns a fixed number between 1 and 100

37. EFFECT (प्रभाव)

  • Question:=EFFECT(0.08, 12):
    • a) Calculates the nominal annual interest rate
    • b) Calculates the effective annual interest rate
    • c) Calculates the average interest rate
    • d) Calculates the annual interest on a loan

38. INTEREST (ब्याज)

  • Question:=INTEREST(10000, 0.05, 12):
    • a) Calculates the principal amount of the loan
    • b) Calculates the interest on a loan or investment
    • c) Calculates the future value of an investment
    • d) Calculates the nominal rate of interest

39. NOMINAL (नाममात्र)

  • Question:=NOMINAL(0.05, 12):
    • a) Returns the effective annual interest rate
    • b) Returns the nominal annual interest rate
    • c) Returns the compound annual interest rate
    • d) Returns the total interest for a year

40. RATE (दर)

  • Question:=RATE(12, -100, 1200):
    • a) Calculates the future value of an investment
    • b) Calculates the rate of return on an investment
    • c) Calculates the interest rate per period of an annuity
    • d) Calculates the effective annual interest rate

41. PMT (किस्त)

  • Question:=PMT(0.05/12, 12, 1000):
    • a) Calculates the total amount of a loan
    • b) Calculates the monthly payment for a loan
    • c) Calculates the total interest paid on a loan
    • d) Calculates the future value of an investment

42. IPMT (ब्याज भुगतान)

  • Question:=IPMT(0.05/12, 1, 12, 1000):
    • a) Calculates the total principal paid in the first period
    • b) Calculates the interest payment for a given period
    • c) Calculates the future value of an investment
    • d) Calculates the total amount paid in the first period

43. CUMIPMT (संचयी ब्याज भुगतान)

  • Question:=CUMIPMT(0.05/12, 12, 1000, 1, 12, 0):
    • a) Calculates the total principal paid between two periods
    • b) Calculates the cumulative interest paid between two periods
    • c) Calculates the future value of an investment
    • d) Calculates the total amount paid over the entire loan period

44. CUMPRINC (संचयी प्रिंसिपल)

  • Question:=CUMPRINC(0.05/12, 12, 1000, 1, 12, 0):
    • a) Calculates the cumulative interest paid between two periods
    • b) Calculates the total interest paid over the entire loan period
    • c) Calculates the cumulative principal paid between two periods
    • d) Calculates the future value of an investment

45. PV (वर्तमान मूल्य)

  • Question:=PV(0.05, 12, -100):
    • a) Calculates the future value of an investment
    • b) Calculates the present value of an investment
    • c) Calculates the total interest paid over the entire loan period
    • d) Calculates the cumulative interest paid between two periods

46. FV (भविष्य मूल्य)

  • Question:=FV(0.05, 12, -100):
    • a) Calculates the total amount of a loan
    • b) Calculates the future value of an investment
    • c) Calculates the interest paid over the entire loan period
    • d) Calculates the present value of an investment

47. YEARFRAC (वर्षीय अंश)

  • Question:=YEARFRAC(A1, B1):
    • a) Calculates the number of days between A1 and B1
    • b) Calculates the fraction of the year between A1 and B1
    • c) Calculates the number of months between A1 and B1
    • d) Calculates the number of years between A1 and B1

48. NPV (शुद्ध वर्तमान मूल्य)

  • Question:=NPV(0.05, C1:C5):
    • a) Calculates the future value of an investment
    • b) Calculates the net present value of an investment
    • c) Calculates the total amount of the investment
    • d) Calculates the annual interest rate of the investment

49. IRR (आंतरिक लाभदायक दर)

  • Question:=IRR(D1:D5):
    • a) Calculates the future value of a series of cash flows
    • b) Calculates the internal rate of return for a series of cash flows
    • c) Calculates the total amount of cash flows
    • d) Calculates the average rate of return on an investment

50. HYPERLINK (हाइपरलिंक)

  • Question:=HYPERLINK("http://www.example.com", "Click Here"):
    • a) Creates a hyperlink that displays “Click Here” and links to “http://www.example.com“
    • b) Creates a hyperlink to a local file
    • c) Creates a hyperlink that displays the URL itself
    • d) Creates a hyperlink with no text

51. AVERAGEIF (औसत यदि)

  • Question:=AVERAGEIF(A1:A10, ">50", B1:B10):
    • a) Calculates the average of B1where corresponding A1values are greater than 50
    • b) Calculates the average of A1where B1values are greater than 50
    • c) Calculates the sum of B1where A1values are greater than 50
    • d) Calculates the average of A1regardless of conditions

52. COUNTIF (गिनती यदि)

  • Question:=COUNTIF(A1:A10, ">50"):
    • a) Counts the number of cells in A1that are greater than 50
    • b) Counts the number of cells in A1that are less than 50
    • c) Counts the number of cells in A1that equal 50
    • d) Counts the number of cells in B1that are greater than 50

53. SUMIF (योग यदि)

  • Question:=SUMIF(A1:A10, ">50", B1:B10):
    • a) Sums the values in B1where corresponding A1values are greater than 50
    • b) Sums the values in A1where B1values are greater than 50
    • c) Sums the values in B1where A1values are less than 50
    • d) Sums the values in A1regardless of conditions

54. DCOUNT (डेटा गिनती)

  • Question:=DCOUNT(A1:C10, "Sales", C1:C10):
    • a) Counts the number of cells in the “Sales” column
    • b) Counts the number of numeric entries in the specified range
    • c) Counts the number of cells in the range C1
    • d) Counts the number of entries in column C that match the criteria in A1

55. DGET (डेटा प्राप्त करें)

  • Question:=DGET(A1:C10, "Sales", A1:B2):
    • a) Retrieves a single value from the “Sales” column based on criteria in A1
    • b) Retrieves the sum of values in the “Sales” column
    • c) Retrieves the average value of “Sales” column
    • d) Retrieves the maximum value from the “Sales” column

56. DAVG (डेटा औसत)

  • Question:=DAVG(A1:C10, "Sales", A1:B2):
    • a) Calculates the average of the “Sales” column based on criteria in A1
    • b) Calculates the sum of values in the “Sales” column
    • c) Calculates the maximum value of the “Sales” column
    • d) Calculates the count of entries in the “Sales” column

57. DSTDEV (डेटा मानक विचलन)

  • Question:=DSTDEV(A1:C10, "Sales", A1:B2):
    • a) Calculates the standard deviation of the “Sales” column based on criteria in A1
    • b) Calculates the average of the “Sales” column
    • c) Calculates the maximum value of the “Sales” column
    • d) Calculates the sum of the “Sales” column

58. DB (डेटा बुक)

  • Question:=DB(1000, 0.05, 5, 3):
    • a) Calculates the depreciation of an asset using the fixed-declining balance method
    • b) Calculates the total cost of an asset
    • c) Calculates the future value of an investment
    • d) Calculates the interest payment of a loan

59. DURATION (अवधि)

  • Question:=DURATION(0.05, 10, 1000, 12):
    • a) Calculates the duration of a bond
    • b) Calculates the maturity value of a bond
    • c) Calculates the interest rate of a bond
    • d) Calculates the present value of a bond

60. XNPV (एक्सएनपीवी)

  • Question:=XNPV(0.05, D1:D5, E1:E5):
    • a) Calculates the net present value of cash flows with specified dates
    • b) Calculates the future value of cash flows with specified dates
    • c) Calculates the interest rate of cash flows
    • d) Calculates the average of cash flows

61. XIRR (एक्सआईआरआर)

  • Question:=XIRR(D1:D5, E1:E5):
    • a) Calculates the internal rate of return for a series of cash flows with specific dates
    • b) Calculates the future value of cash flows with specific dates
    • c) Calculates the net present value of cash flows with specific dates
    • d) Calculates the total return on investment

62. COUNTBLANK (खाली गिनती)

  • Question:=COUNTBLANK(A1:A10):
    • a) Counts the number of non-empty cells in A1
    • b) Counts the number of blank cells in A1
    • c) Counts the number of numeric cells in A1
    • d) Counts the number of text cells in A1

63. COUNTA (गिनती कीजिए)

  • Question:=COUNTA(A1:A10):
    • a) Counts the number of empty cells in A1
    • b) Counts the number of cells that contain text or numbers in A1
    • c) Counts the number of numeric cells in A1
    • d) Counts the number of cells with errors in A1

64. CHOOSE (चुनें)

  • Question:=CHOOSE(2, "Red", "Green", "Blue"):
    • a) Returns “Red”
    • b) Returns “Green”
    • c) Returns “Blue”
    • d) Returns the index number

65. INDEX (सूचकांक)

  • Question:=INDEX(A1:B10, 3, 2):
    • a) Returns the value in the third row, second column of A1
    • b) Returns the value in the first row, second column of A1
    • c) Returns the value in the third row, first column of A1
    • d) Returns the value in the last row, last column of A1

66. MATCH (मेल)

  • Question:=MATCH("Apple", A1:A10, 0):
    • a) Returns the position of “Apple” in A1
    • b) Returns the value of “Apple” in A1
    • c) Returns the number of occurrences of “Apple” in A1
    • d) Returns the sum of values in A1

67. OFFSET (स्थानांतर)

  • Question:=OFFSET(A1, 2, 3):
    • a) Returns the value of the cell that is 2 rows down and 3 columns to the right of A1
    • b) Returns the value of the cell that is 2 rows up and 3 columns to the left of A1
    • c) Returns the value of the cell that is 2 rows up and 3 columns to the right of A1
    • d) Returns the value of the cell that is 2 rows down and 3 columns to the left of A1

68. INDIRECT (अप्रत्यक्ष)

  • Question:=INDIRECT("A1"):
    • a) Returns the value of cell A1
    • b) Returns the address of cell A1
    • c) Returns the formula in cell A1
    • d) Returns the name of the sheet where cell A1 is located

69. SUBTOTAL (उपकुल)

  • Question:=SUBTOTAL(1, A1:A10):
    • a) Calculates the average of A1
    • b) Calculates the sum of A1
    • c) Calculates the count of A1
    • d) Calculates the maximum value of A1

70. TRANSPOSE (पारगमन)

  • Question:=TRANSPOSE(A1:B2):
    • a) Converts the rows of A1into columns
    • b) Converts the columns of A1into rows
    • c) Calculates the average of A1
    • d) Flattens the range A1into a single column

71. UNIQUE (अद्वितीय)

  • Question:=UNIQUE(A1:A10):
    • a) Returns the unique values from A1
    • b) Returns the duplicate values from A1
    • c) Returns the sorted values from A1
    • d) Returns the sum of A1

72. FILTER (फ़िल्टर)

  • Question:=FILTER(A1:B10, B1:B10 > 50):
    • a) Returns the values from A1where corresponding B1values are greater than 50
    • b) Returns the values from B1where corresponding A1values are greater than 50
    • c) Returns the filtered values from A1based on conditions
    • d) Returns the number of values greater than 50 in B1

73. SORT (सॉर्ट)

  • Question:=SORT(A1:B10, 1, TRUE):
    • a) Sorts A1by the first column in ascending order
    • b) Sorts A1by the second column in descending order
    • c) Sorts A1by the first column in descending order
    • d) Sorts A1by the second column in ascending order

74. SPLIT (विभाजित करें)

  • Question:=SPLIT(A1, ","):
    • a) Splits the text in A1 into separate cells based on commas
    • b) Splits the text in A1 into separate cells based on spaces
    • c) Splits the text in A1 into rows based on commas
    • d) Splits the text in A1 into columns based on spaces

75. TEXTJOIN (टेक्स्ट जॉइन)

  • Question:=TEXTJOIN(", ", TRUE, A1:A3):
    • a) Joins the text from A1with commas and spaces
    • b) Joins the text from A1with dashes
    • c) Joins the text from A1without any delimiter
    • d) Joins the text from A1with spaces only

76. CONCAT (कनकैट)

  • Question:=CONCAT(A1, " ", B1):
    • a) Joins the text in A1 and B1 with a space in between
    • b) Joins the text in A1 and B1 without any space
    • c) Joins the text in A1 and B1 with a comma
    • d) Joins the text in A1 and B1 with a dash

77. FIND (खोजें)

  • Question:=FIND("apple", A1):
    • a) Returns the position of the first occurrence of “apple” in A1
    • b) Returns the position of the last occurrence of “apple” in A1
    • c) Returns the number of occurrences of “apple” in A1
    • d) Returns the text of “apple” in A1

78. SEARCH (खोजें)

  • Question:=SEARCH("apple", A1):
    • a) Returns the position of the first occurrence of “apple” in A1, case-insensitive
    • b) Returns the position of the first occurrence of “apple” in A1, case-sensitive
    • c) Returns the number of occurrences of “apple” in A1
    • d) Returns the text of “apple” in A1

79. REPLACE (विकल्प)

  • Question:=REPLACE(A1, 1, 5, "new"):
    • a) Replaces the first 5 characters in A1 with “new”
    • b) Replaces the last 5 characters in A1 with “new”
    • c) Replaces characters from position 5 with “new”
    • d) Replaces characters starting from position 1 with “new”

80. SUBSTITUTE (परिवर्तन)

  • Question:=SUBSTITUTE(A1, "old", "new"):
    • a) Replaces all occurrences of “old” with “new” in A1
    • b) Replaces only the first occurrence of “old” with “new” in A1
    • c) Replaces all occurrences of “new” with “old” in A1
    • d) Replaces only the first occurrence of “new” with “old” in A1

81. PROPER (उचित)

  • Question:=PROPER(A1):
    • a) Converts the text in A1 to uppercase
    • b) Converts the text in A1 to lowercase
    • c) Converts the text in A1 to proper case (capitalize first letter of each word)
    • d) Converts the text in A1 to sentence case

82. UPPER (ऊपर)

  • Question:=UPPER(A1):
    • a) Converts the text in A1 to uppercase
    • b) Converts the text in A1 to lowercase
    • c) Converts the text in A1 to proper case
    • d) Converts the text in A1 to sentence case

83. LOWER (नीचे)

  • Question:=LOWER(A1):
    • a) Converts the text in A1 to uppercase
    • b) Converts the text in A1 to lowercase
    • c) Converts the text in A1 to proper case
    • d) Converts the text in A1 to sentence case

84. TRIM (ट्रिम)

  • Question:=TRIM(A1):
    • a) Removes all spaces from text in A1
    • b) Removes leading and trailing spaces from text in A1
    • c) Removes all non-printable characters from text in A1
    • d) Removes extra spaces between words in text in A1

85. CLEAN (साफ)

  • Question:=CLEAN(A1):
    • a) Removes all spaces from text in A1
    • b) Removes leading and trailing spaces from text in A1
    • c) Removes all non-printable characters from text in A1
    • d) Removes extra spaces between words in text in A1

86. TEXT (पाठ)

  • Question:=TEXT(A1, "0.00"):
    • a) Formats the value in A1 as a number with two decimal places
    • b) Formats the value in A1 as a currency
    • c) Formats the value in A1 as a date
    • d) Formats the value in A1 as a percentage

87. VALUE (मूल्य)

  • Question:=VALUE(A1):
    • a) Converts text in A1 to a number
    • b) Converts a number in A1 to text
    • c) Converts the value in A1 to a percentage
    • d) Converts the value in A1 to a date

88. NUMBERVALUE (संख्यात्मक मूल्य)

  • Question:=NUMBERVALUE(A1, ".", ","):
    • a) Converts text in A1 to a number using specified decimal and thousands separators
    • b) Converts a number in A1 to text using specified decimal and thousands separators
    • c) Converts text in A1 to a percentage using specified separators
    • d) Converts text in A1 to a date using specified separators

89. FORMULATEXT (सूत्र पाठ)

  • Question:=FORMULATEXT(A1):
    • a) Displays the formula used in cell A1
    • b) Displays the result of the formula in cell A1
    • c) Displays the text value in cell A1
    • d) Displays the cell reference in A1

90. GETPIVOTDATA (पिवट डेटा प्राप्त करें)

  • Question:=GETPIVOTDATA("Sales", A1):
    • a) Retrieves the sales data from a PivotTable based on cell A1
    • b) Retrieves the text value from cell A1 in a PivotTable
    • c) Retrieves the formula used in cell A1 in a PivotTable
    • d) Retrieves the cell reference from a PivotTable

91. ARRAYFORMULA (एरेफार्मूला)

  • Question:=ARRAYFORMULA(A1:A10 * B1:B10):
    • a) Applies the multiplication operation to each element in A1and B1
    • b) Calculates the average of A1and B1
    • c) Summarizes the range A1and B1
    • d) Returns the total count of elements in A1and B1

92. SEQUENCE (अनुक्रम)

  • Question:=SEQUENCE(5, 3):
    • a) Creates a 5-row by 3-column array of sequential numbers
    • b) Creates a single-row array of 5 sequential numbers
    • c) Creates a 3-row by 5-column array of sequential numbers
    • d) Creates a single-column array of 5 sequential numbers

93. SPLIT (विभाजित करें)

  • Question:=SPLIT(A1, ","):
    • a) Splits the text in A1 into separate cells based on commas
    • b) Splits the text in A1 into separate cells based on spaces
    • c) Splits the text in A1 into rows based on commas
    • d) Splits the text in A1 into columns based on spaces

94. REGEXMATCH (रेगेक्स मेल)

  • Question:=REGEXMATCH(A1, "^[A-Za-z]+$"):
    • a) Checks if A1 contains only letters
    • b) Checks if A1 contains numbers
    • c) Checks if A1 contains letters or numbers
    • d) Checks if A1 contains special characters

95. REGEXEXTRACT (रेगेक्स निकालें)

  • Question:=REGEXEXTRACT(A1, "\d{3}"):
    • a) Extracts the first three-digit number from A1
    • b) Extracts the first letter from A1
    • c) Extracts the first occurrence of a digit from A1
    • d) Extracts the first word from A1

96. REGEXREPLACE (रेगेक्स बदलें)

  • Question:=REGEXREPLACE(A1, "\d", "#"):
    • a) Replaces all digits in A1 with “#”
    • b) Replaces all letters in A1 with “#”
    • c) Replaces all non-numeric characters in A1 with “#”
    • d) Replaces all spaces in A1 with “#”

97. XMATCH (एक्समैच)

  • Question:=XMATCH("Apple", A1:A10):
    • a) Returns the relative position of “Apple” in A1
    • b) Returns the absolute position of “Apple” in A1
    • c) Returns the value of “Apple” in A1

98. FILTER (फ़िल्टर)

  • Question:=FILTER(A1:A10, B1:B10 = "Completed"):
    • a) Filters the values in A1where corresponding B1is “Completed”
    • b) Filters the values in B1where corresponding A1is “Completed”
    • c) Filters the values in A1where B1is not “Completed”
    • d) Filters the values in A1based on whether B1contains a number

99. SEQUENCE (अनुक्रम)

  • Question:=SEQUENCE(4, 2, 1, 2):
    • a) Creates a 4-row by 2-column array starting from 1 with a step of 2
    • b) Creates a 2-row by 4-column array starting from 1 with a step of 2
    • c) Creates a 4-row by 2-column array starting from 1 with a step of 1
    • d) Creates a single-row array of 4 sequential numbers starting from 1

100. UNIQUE (अद्वितीय)

  • Question:=UNIQUE(A1:A10):
    • a) Returns the unique values from A1
    • b) Returns the duplicate values from A1
    • c) Returns the sorted values from A1
    • d) Returns the count of unique values in A1

Answers:

  1. SUM (योग) – a) Calculates the total sum of a range of cells.
  2. AVERAGE (औसत) – a) Calculates the average of a range of cells.
  3. COUNT (गिनती) – a) Counts the number of numeric entries in a range.
  4. MAX (अधिकतम) – a) Finds the highest value in a range of cells.
  5. MIN (न्यूनतम) – a) Finds the lowest value in a range of cells.
  6. IF (यदि) – a) Returns one value if a condition is true and another if false.
  7. VLOOKUP (वी लुकअप) – a) Searches for a value in the first column of a range and returns a value in the same row from another column.
  8. HLOOKUP (एच लुकअप) – a) Searches for a value in the first row of a range and returns a value in the same column from another row.
  9. INDEX (सूचकांक) – a) Returns the value in a specified row and column of a range.
  10. MATCH (मेल) – a) Searches for a value in a range and returns the relative position of that item.
  11. CONCATENATE (संयोजन) – a) Joins multiple text strings into one string.
  12. LEFT (बायाँ) – a) Returns a specified number of characters from the start of a text string.
  13. RIGHT (दायाँ) – a) Returns a specified number of characters from the end of a text string.
  14. MID (मध्य) – a) Returns a specific number of characters from a text string, starting at the position you specify.
  15. TRIM (ट्रिम) – b) Removes leading and trailing spaces from text.
  16. UPPER (ऊपर) – a) Converts all letters in a text string to uppercase.
  17. LOWER (नीचे) – b) Converts all letters in a text string to lowercase.
  18. PROPER (उचित) – c) Capitalizes the first letter of each word in a text string.
  19. SUBSTITUTE (परिवर्तन) – a) Replaces occurrences of a specified text string within another text string.
  20. REPLACE (विकल्प) – a) Replaces part of a text string with a different text string.
  21. LEN (लंबाई) – a) Returns the number of characters in a text string.
  22. FIND (खोजें) – a) Returns the position of a substring within a text string.
  23. SEARCH (खोजें) – a) Returns the position of a substring within a text string, case-insensitive.
  24. TEXT (पाठ) – a) Formats a number and converts it to text in a specified format.
  25. VALUE (मूल्य) – a) Converts text that appears in a recognized format into a numeric value.
  26. DATE (तारीख) – a) Returns the serial number of a specific date.
  27. TIME (समय) – a) Returns the serial number of a specific time.
  28. NOW (अब) – a) Returns the current date and time.
  29. TODAY (आज) – a) Returns the current date.
  30. YEAR (साल) – a) Extracts the year from a date.
  31. MONTH (महीना) – a) Extracts the month from a date.
  32. DAY (दिन) – a) Extracts the day from a date.
  33. HOUR (घंटा) – a) Extracts the hour from a time.
  34. MINUTE (मिनट) – a) Extracts the minute from a time.
  35. SECOND (सेकंड) – a) Extracts the second from a time.
  36. SUMIF (योग यदि) – a) Sums the values in a range based on a condition.
  37. COUNTIF (गिनती यदि) – a) Counts the number of cells that meet a condition.
  38. AVERAGEIF (औसत यदि) – a) Calculates the average of values that meet a condition.
  39. ROUND (गोल) – a) Rounds a number to a specified number of digits.
  40. CEILING (छत) – a) Rounds a number up to the nearest multiple of significance.
  41. FLOOR (फ्लोर) – a) Rounds a number down to the nearest multiple of significance.
  42. RAND (रैंड) – a) Returns a random number between 0 and 1.
  43. RANDBETWEEN (रैंडबेटवीन) – a) Returns a random number between two specified numbers.
  44. PMT (पीएमटी) – a) Calculates the payment for a loan based on constant payments and a constant interest rate.
  45. FV (एफवी) – a) Calculates the future value of an investment based on periodic, constant payments and a constant interest rate.
  46. NPV (एनपीवी) – a) Calculates the net present value of an investment based on a series of cash flows and a discount rate.
  47. IRR (आंतरिक लाभदायक दर) – b) Calculates the internal rate of return for a series of cash flows.
  48. HYPERLINK (हाइपरलिंक) – a) Creates a hyperlink that displays “Click Here” and links to “http://www.example.com“.
  49. AVERAGEIF (औसत यदि) – a) Calculates the average of B1where corresponding A1values are greater than 50.
  50. COUNTIF (गिनती यदि) – a) Counts the number of cells in A1that are greater than 50.
  51. SUMIF (योग यदि) – a) Sums the values in B1where corresponding A1values are greater than 50.
  52. DCOUNT (डेटा गिनती) – b) Counts the number of numeric entries in the specified range.
  53. DGET (डेटा प्राप्त करें) – a) Retrieves a single value from the “Sales” column based on criteria in A1.
  54. DAVG (डेटा औसत) – a) Calculates the average of the “Sales” column based on criteria in A1.
  55. DSTDEV (डेटा मानक विचलन) – a) Calculates the standard deviation of the “Sales” column based on criteria in A1.
  56. DB (डेटा बुक) – a) Calculates the depreciation of an asset using the fixed-declining balance method.
  57. DURATION (अवधि) – a) Calculates the duration of a bond.
  58. XNPV (एक्सएनपीवी) – a) Calculates the net present value of cash flows with specified dates.
  59. XIRR (एक्सआईआरआर) – a) Calculates the internal rate of return for a series of cash flows with specific dates.
  60. COUNTBLANK (खाली गिनती) – b) Counts the number of blank cells in A1.
  61. COUNTA (गिनती कीजिए) – b) Counts the number of cells that contain text or numbers in A1.
  62. CHOOSE (चुनें) – b) Returns “Green”.
  63. INDEX (सूचकांक) – a) Returns the value in the third row, second column of A1.
  64. MATCH (मेल) – a) Returns the position of “Apple” in A1.
  65. OFFSET (स्थानांतर) – a) Returns the value of the cell that is 2 rows down and 3 columns to the right of A1.
  66. INDIRECT (अप्रत्यक्ष) – a) Returns the value of the cell referenced by the text string in A1.
  67. FORMULATEXT (सूत्र पाठ) – a) Displays the formula used in cell A1.
  68. GETPIVOTDATA (पिवट डेटा प्राप्त करें) – a) Retrieves the sales data from a PivotTable based on cell A1.
  69. SUBTOTAL (उपयोगकर्ता कुल) – a) Calculates the average of A1.
  70. TRANSPOSE (पारगमन) – a) Converts the rows of A1into columns.
  71. UNIQUE (अद्वितीय) – a) Returns the unique values from A1.
  72. FILTER (फ़िल्टर) – a) Returns the values from A1where corresponding B1values are greater than 50.
  73. SORT (सॉर्ट) – a) Sorts A1by the first column in ascending order.
  74. SPLIT (विभाजित करें) – a) Splits the text in A1 into separate cells based on commas.
  75. TEXTJOIN (टेक्स्ट जॉइन) – a) Joins the text from A1with commas and spaces.
  76. CONCAT (कनकैट) – a) Joins the text in A1 and B1 with a space in between.
  77. FIND (खोजें) – a) Returns the position of the first occurrence of “apple” in A1.
  78. SEARCH (खोजें) – a) Returns the position of the first occurrence of “apple” in A1, case-insensitive.
  79. REPLACE (विकल्प) – a) Replaces the first 5 characters in A1 with “new”.
  80. SUBSTITUTE (परिवर्तन) – a) Replaces all occurrences of “old” with “new” in A1.
  81. PROPER (उचित) – c) Converts the text in A1 to proper case (capitalize first letter of each word).
  82. UPPER (ऊपर) – a) Converts the text in A1 to uppercase.
  83. LOWER (नीचे) – b) Converts the text in A1 to lowercase.
  84. TRIM (ट्रिम) – b) Removes leading and trailing spaces from text in A1.
  85. CLEAN (साफ) – c) Removes all non-printable characters from text in A1.
  86. TEXT (पाठ) – a) Formats the value in A1 as a number with two decimal places.
  87. VALUE (मूल्य) – a) Converts text in A1 to a number.
  88. NUMBERVALUE (संख्यात्मक मूल्य) – a) Converts text in A1 to a number using specified decimal and thousands separators.
  89. FORMULATEXT (सूत्र पाठ) – a) Displays the formula used in cell A1.
  90. GETPIVOTDATA (पिवट डेटा प्राप्त करें) – a) Retrieves the sales data from a PivotTable based on cell A1.
  91. ARRAYFORMULA (एरेफार्मूला) – a) Applies the multiplication operation to each element in A1and B1.
  92. SEQUENCE (अनुक्रम) – a) Creates a 5-row by 3-column array of sequential numbers.
  93. SPLIT (विभाजित करें) – a) Splits the text in A1 into separate cells based on commas.
  94. REGEXMATCH (रेगेक्स मेल) – a) Checks if A1 contains only letters.
  95. REGEXEXTRACT (रेगेक्स निकालें) – a) Extracts the first three-digit number from A1.
  96. REGEXREPLACE (रेगेक्स बदलें) – a) Replaces all digits in A1 with “#”.
  97. XMATCH (एक्समैच) – a) Returns the relative position of “Apple” in A1.
  98. FILTER (फ़िल्टर) – a) Filters the values in A1where corresponding B1is “Completed”.
  99. SEQUENCE (अनुक्रम) – a) Creates a 4-row by 2-column array starting from 1 with a step of 2.
  100. UNIQUE (अद्वितीय) – a) Returns the unique values from A1.

FAQ of CCC and Compitative Exam

1. What is the full form of CCC? (CCC का पूरा नाम क्या है?)

  • Answer: CCC stands for Course on Computer Concepts. (CCC का मतलब है कंप्यूटर अवधारणाओं पर पाठ्यक्रम।)

2. What are the basic computer skills required for CCC? (CCC के लिए आवश्यक मूलभूत कंप्यूटर कौशल कौन से हैं?)

  • Answer: Basic skills include understanding of operating systems, word processing, spreadsheets, and internet usage. (मूलभूत कौशल में ऑपरेटिंग सिस्टम, वर्ड प्रोसेसिंग, स्प्रेडशीट्स, और इंटरनेट उपयोग की समझ शामिल है।)

3. How is the CCC exam structured? (CCC परीक्षा की संरचना कैसी होती है?)

  • Answer: The CCC exam typically consists of multiple-choice questions (MCQs) covering various computer concepts. (CCC परीक्षा में आमतौर पर कई विकल्प वाले प्रश्न होते हैं जो विभिन्न कंप्यूटर अवधारणाओं को कवर करते हैं।)

4. What is the duration of the CCC exam? (CCC परीक्षा की अवधि कितनी होती है?)

  • Answer: The CCC exam is usually 1 hour 30 minutes long. (CCC परीक्षा आमतौर पर 1 घंटा 30 मिनट की होती है।)

5. What topics are covered in the CCC syllabus? (CCC के सिलेबस में कौन से विषय शामिल हैं?)

  • Answer: The syllabus covers computer fundamentals, operating systems, word processing, spreadsheets, presentations, and internet basics. (सिलेबस में कंप्यूटर के मूल बातें, ऑपरेटिंग सिस्टम, वर्ड प्रोसेसिंग, स्प्रेडशीट्स, प्रस्तुतियाँ, और इंटरनेट की बुनियादी बातें शामिल हैं।)

6. What is the passing criteria for CCC exam? (CCC परीक्षा के लिए पास होने के मानदंड क्या हैं?)

  • Answer: Candidates need to score at least 50% marks to pass the CCC exam. (उम्मीदवारों को CCC परीक्षा में पास होने के लिए कम से कम 50% अंक प्राप्त करने की आवश्यकता होती है।)

7. Is there any practical component in the CCC exam? (CCC परीक्षा में कोई प्रायोगिक घटक है क्या?)

  • Answer: No, the CCC exam is purely theoretical with multiple-choice questions. (नहीं, CCC परीक्षा पूरी तरह से सैद्धांतिक होती है जिसमें कई विकल्प वाले प्रश्न होते हैं।)

8. How can one prepare for the CCC exam effectively? (CCC परीक्षा की प्रभावी तैयारी कैसे करें?)

  • Answer: Review the syllabus, practice past papers, and use online resources or study guides. (सिलेबस की समीक्षा करें, पिछले पेपरों का अभ्यास करें, और ऑनलाइन संसाधनों या अध्ययन गाइड का उपयोग करें।)

9. What is LibreOffice Calc? (LibreOffice Calc क्या है?)

  • Answer: LibreOffice Calc is a spreadsheet program that allows users to create and manipulate data in tables and perform calculations. (LibreOffice Calc एक स्प्रेडशीट प्रोग्राम है जो उपयोगकर्ताओं को तालिकाओं में डेटा बनाने और संशोधित करने और गणनाएँ करने की अनुमति देता है।)

10. How to create a formula in LibreOffice Calc? (LibreOffice Calc में एक सूत्र कैसे बनाएं?)

  • Answer: Enter = followed by the formula in a cell to create a formula, for example, =A1+B1. (एक सेल में सूत्र बनाने के लिए = दर्ज करें और उसके बाद सूत्र लिखें, उदाहरण के लिए, =A1+B1।)

11. What is the purpose of using VLOOKUP in Excel? (Excel में VLOOKUP का उपयोग करने का उद्देश्य क्या है?)

  • Answer: VLOOKUP is used to search for a value in the first column of a table and return a value in the same row from another column. (VLOOKUP का उपयोग एक तालिका के पहले कॉलम में एक मान खोजने और उसी पंक्ति में किसी अन्य कॉलम से एक मान लौटाने के लिए किया जाता है।)

12. How to use the SUM function in Excel? (Excel में SUM फ़ंक्शन का उपयोग कैसे करें?)

  • Answer: Use =SUM(A1:A10) to add all the numbers in the range A1 to A10. (A1 से A10 तक सभी संख्याओं को जोड़ने के लिए =SUM(A1:A10) का उपयोग करें।)

13. What is the role of the ‘IF’ function in Excel? (Excel में ‘IF’ फ़ंक्शन की भूमिका क्या है?)

  • Answer: The ‘IF’ function checks a condition and returns one value if the condition is true and another value if the condition is false. (IF फ़ंक्शन एक शर्त की जांच करता है और यदि शर्त सही है तो एक मान लौटाता है और यदि शर्त गलत है तो दूसरा मान लौटाता है।)

14. What is the difference between Windows 10 and Windows 11? (Windows 10 और Windows 11 में क्या अंतर है?)

  • Answer: Windows 11 has a new user interface, improved performance, and enhanced security features compared to Windows 10. (Windows 11 में नए यूजर इंटरफेस, बेहतर प्रदर्शन, और Windows 10 की तुलना में उन्नत सुरक्षा विशेषताएँ हैं।)

15. How to secure your computer from viruses? (अपने कंप्यूटर को वायरस से कैसे सुरक्षित करें?)

  • Answer: Install antivirus software, keep your system updated, and avoid downloading files from untrusted sources. (एंटीवायरस सॉफ़्टवेयर इंस्टॉल करें, अपने सिस्टम को अपडेट रखें, और अविश्वसनीय स्रोतों से फ़ाइलें डाउनलोड करने से बचें।)

16. What is a firewall and why is it important? (फायरवॉल क्या है और यह क्यों महत्वपूर्ण है?)

  • Answer: A firewall is a network security system that monitors and controls incoming and outgoing network traffic based on predetermined security rules. (फायरवॉल एक नेटवर्क सुरक्षा प्रणाली है जो पूर्वनिर्धारित सुरक्षा नियमों के आधार पर इनकमिंग और आउटगोइंग नेटवर्क ट्रैफ़िक की निगरानी और नियंत्रण करता है।)

17. How can you create a pivot table in Excel? (Excel में पिवट टेबल कैसे बनाएं?)

  • Answer: Select your data, go to the “Insert” tab, and click on “PivotTable” to create a pivot table. (अपने डेटा का चयन करें, “Insert” टैब पर जाएं, और “PivotTable” पर क्लिक करके पिवट टेबल बनाएं।)

18. What is the Internet of Things (IoT)? (इंटरनेट ऑफ थिंग्स (IoT) क्या है?)

  • Answer: IoT refers to the network of interconnected devices that can communicate and share data with each other. (IoT उन इंटरकनेक्टेड डिवाइसेस के नेटवर्क को संदर्भित करता है जो एक-दूसरे के साथ संवाद कर सकते हैं और डेटा साझा कर सकते हैं।)

19. What is the purpose of the ‘CONCATENATE’ function in Excel? (Excel में ‘CONCATENATE’ फ़ंक्शन का उद्देश्य क्या है?)

  • Answer: The ‘CONCATENATE’ function joins together two or more text strings into one. (CONCATENATE फ़ंक्शन दो या अधिक टेक्स्ट स्ट्रिंग्स को एक में जोड़ता है।)

20. How to use the ‘COUNTIF’ function in Excel? (Excel में ‘COUNTIF’ फ़ंक्शन का उपयोग कैसे करें?)

  • Answer: Use =COUNTIF(range, criteria) to count the number of cells within a range that meet a specified condition. (एक निर्दिष्ट शर्त को पूरा करने वाली सेल की संख्या गिनने के लिए =COUNTIF(range, criteria) का उपयोग करें।)

21. What are macros in Excel? (Excel में मैक्रोज़ क्या हैं?)

  • Answer: Macros are sequences of instructions that automate repetitive tasks in Excel. (मैक्रोज़ Excel में दोहराए जाने वाले कार्यों को स्वचालित करने के लिए निर्देशों की श्रृंखलाएँ हैं।)

22. How to protect an Excel worksheet with a password? (Excel वर्कशीट को पासवर्ड से कैसे सुरक्षित करें?)

  • Answer: Go to the “Review” tab, click “Protect Sheet,” and enter a password to protect your worksheet. (“Review” टैब पर जाएं, “Protect Sheet” पर क्लिक करें, और अपनी वर्कशीट की सुरक्षा के लिए एक पासवर्ड दर्ज करें।)

23. What is a database management system (DBMS)? (डाटाबेस प्रबंधन प्रणाली (DBMS) क्या है?)

  • Answer: A DBMS is software that allows users to create, manage, and interact with databases. (DBMS एक सॉफ़्टवेयर है जो उपयोगकर्ताओं को डाटाबेस बनाने, प्रबंधित करने, और बातचीत करने की अनुमति देता है।)

24. How can you use the ‘HYPERLINK’ function in Excel? (Excel में ‘HYPERLINK’ फ़ंक्शन का उपयोग कैसे करें?)

  • Answer: Use =HYPERLINK("URL", "Link Text") to create a clickable link to a URL. (एक क्लिक करने योग्य लिंक बनाने के लिए =HYPERLINK("URL", "Link Text") का उपयोग करें।)

25. What is the use of the ‘TEXT’ function in Excel? (Excel में ‘TEXT’ फ़ंक्शन का उपयोग क्या है?)

  • Answer: The ‘TEXT’ function formats a number and converts it to text in a specified format. (TEXT फ़ंक्शन एक संख्या को प्रारूपित करता है और इसे निर्दिष्ट प्रारूप में टेक्स्ट में बदलता है।)