Function

1. The SUM function returns the value 10 instead of 14. Why the 4 is not included?

Question Error SUM

The SUM function doesn't add cells with a background color

The SUM function doesn't add cell in bold

The cell B4 is not in included in the range to sum

The SUM function doesn't add text

2. What is the result of the following formula?

Question result of the formula

395

110

225

300

3. The following formulae use the basic mathematics operator (+, -, *, /) and parentheses. Which of the following formulae returns 36?

Question which formula returns 36

Row 2

Row 3

Row 4

Row 5

4. We give a bonus of 200$ for all the employees who have sold 20 units or more. Which formula do the job?

Question Function to calculate the bonus

=IF(B<20,0,200)

=IF(B2>=20,0,200)

=IF(B2>=20,200,0)

=IF(B2<20,200,)

=IF(B;20,0,200)

5. Which formula calculates the for each row the Total?

Question total of each row

=C2+D2

=C2*D2

=D2*C2

=C2*A2+D2

6. Which result returns the formula =DCOUNT(A1:E13,3,H1:H2)

Question what is the result return by the function DCOUNT

16.5

7. Which functions can transform the text in uppercase?

Question function to transform in uppercase

=UCASE(A2)

=UPPERCASE(A2)

=PROPER(A2)

=UPPER(A2)

8. How to write the DCOUNT function to return the following result?

Question how to wrte the function DCOUNT

=DCOUNT(A:A13,3,H2)

=DCOUNT(A1:E13,3,H1:H2)

=DCOUNT(A1:E13,3,H2)

=DCOUNT(A1:C13,5,H1:H2)

9. What returns the function DSUM in this situation?

Question what returns the function DSUM

16.5

10. You want to plan a meeting for the companies in the cities of Manchester and Bristol with more than 20 employees.

How to write the formula?

Question Select company according to employee and city

Question Select company according to employee and city

=IF(OR(B2="MANCHESTER",B2="BRISTOL",C2>=20),"Meeting","")

=IF(OR(AND(B2="MANCHESTER",B2="BRISTOL"),C2>=20),"Meeting","")

=IF(AND(B2="MANCHESTER",B2="BRISTOL",C2>=20),"Meeting","")

=IF(AND(OR(B2="MANCHESTER",B2="BRISTOL"),C2>=20),"Meeting","")

11. Which formula returns the minimum time worked in the week?

Question find the minimum time

=MINIMUM(B2:F10)

=MIN(B2:F10)

=MINI(B2:B10)+MIN(C2:C10)+MIN(D2:D10)+MIN(E2:E10)+MIN(F2:F10)

=MINI(B2:B10)&MIN(C2:C10)&MIN(D2:D10)&MIN(E2:E10)&MIN(F2:F10)

12. What result returns the formula =VLOOKUP(F5,A2:D18,4,0)

Question what is the result returned by VLOOKUP

#N/A

7.66

Beverage

Ethiopian Ground Coffee

13. Which answer(s) returns the average for each student?

Question Average

=AVERAGE(B2:E8)

=SUM(B2:E8)

=SUM(B2:E8)/5

=SUM(B2:E8)/COUNT(A2:A8)

14. Which Excel function returns the position of a value in a list?

Question position of the item in a list

MATCH

VLOOKUP

XLOOKUP

INDEX

15. How do you write a logical test to select hotels with a Fitness room, with 3 stars classification or more, and the price of the menu is less than 30?

Question write logical test to select hotel

Question write logical test to select hotel

=AND(B2>=3,OR(C2<30,E2="Yes"))

=AND(B2>3,C2<=30,E2="Yes")

=AND(B2>=3,C2<30,E2="Yes")

=XOR(B2>3,C2<30,E2=Yes)

16. What is the result returned by the SUMIFS function

Question what is the result return by the SUMIFS function

03/09/2023

17. This question concerns the position of dollars in a formula.

We want to construct a multiplication table by using the values in the headers, column A, and row 1.

How do you write the formula in B2 to stay on the row 1 and column A?

Question dollar in the reference

=A2*B1

=$A$2*$B$1

=$A2*$B1

=$A2*B$1

=A$2*B$1

18. What is the result returned by the function DATE?

Question DATE function

The first day of the months (01/01/2023, 01/02/2023)

The first days (1, 2, 3, ...) of January 2023

The dates of the first weeks of the year 2023

19. Which formula extracts the room number?

Question how to extract the room number

=LEFT(A2,1)

=RIGHT(A2,1)

=TEXTAFTER(A2,1)

=TEXTBEFORE(A2,-1)

20. What is the result returned by the formula =DATE(B1,B2,1)-1

Question what is the result return by DATE

01/09/2023

00/09/2023

31/08/2023

#VALUE!

21. Which formula or tool can join the name and the first name?

Question how to join the Name and the firstname

=TEXTJOIN(" ",,A2:B2)

=A2&" "&B2

=CONCATENATE(A2:B2)

FlashFill

=TEXTJOIN(A2:B2," ")

22. Which formula returns the number of items where the quantity is greater or equal than 6?

Question quantity greater than 6

=COUNTIF(C2:C13,">=6")

=COUNTIF(C2:C13,6+)

=COUNTIF(>=6,C2:C13)

=COUNTIFS(C2:C13,>=6)

23. How to return the product name according to the Product ID?

Question formula to return the product name

=VLOOKUP(F5,A2:D18,"C")

=VLOOKUP(F5,A2:D18,3,0)

=VLOOKUP(A2:D18,F5,2)

=VLOOKUP(F5,A2:D18,3)

24. What is the result returned by the formula =VLOOKUP(F5,A2:D11,3,0) ?

Question Result of the formula VLOOKUP

#N/A

Detox green tea

3.95

25. Which Excel function removes the spaces before each name?

Question function to remove space

TRIM

SPACE

REMOVESPACE

CLEAR

26. Which Excel function returns the position of the space in the text?

Question find the position of the space in the string

POSITION

LEN

LOCATE

27. In this example, you have the different Vehicle Internal Numbers (VIN)

How to extract the VDS which is the characters between the 4 and 9 position?

Question extract the VDS

=MID(4,A2,9)

=MID(A2,4,6)

=MID(A2,4,9)

=MID(4,9,A2)

Most Viewed Posts

Recent Posts