Question

B с D E F G H Close Call Trading 2 Internal Audit Inspection 1111 Davidson 3/4/17 Sales Invoices 31/3/17 Invoice No Product Unit Price Customer 4 Employee 5 Employee Name 6 Date Performed 7 8 Audit Type 9 Prepared on 10 11 Analysis 12 Sales Invoices Date Shipped 13 14 31/3/17 15 31/3/17 16 31/3/17 17 31/3/17 18 31/3/17 19 31/3/17 20 31/3/17 21 31/3/17 22 31/3/17 23 Total 24 101101 101101 101101 101 102 101102 101103 101104 101104 101104 A10 C30 D10 BOS D10 E50 C30 D10 E50 Quantity Shipped 5 3 4 1 3 $30.30 ABB Alumina $20.10 ABB Alumina $50.50 ABB Alumina $110.00 Lion Bakery $50.50 Lion Bakery $80.80 Golden South $20.10 Cotton Down $50.50 Cotton Down $80.80 Cotton Down Invoice Test Amount $151.50 Correct $100.30 Too high $200.00 Too low $110.00 Correct $100.50 Too low $484.80 Correct $40.20 Correct $220.00 Too high $565.60 Correct $1,972.90 6 2 4 7 35 Quantity Shipped Sale Value 1 5 1 5 11 13 35 Yes $151.50 $110.00 $140.50 $520.50 $1,050.40 $1,972.90 Yes Product Count# 25 26 A10 27 BOS 28 C30 2 29 D10 3 30 E50 2 31 Total 32 Match 33 34 Table 35 Employee# Employee Name Role 36 1000 Amanda Sales Clerk 37 1111 Davidson Internal Auditor 38 2000 James A/R Clerk 39 2222 Majella Internal Auditor 40 3000 Samuel Purchasing Clerk 41 3333 Zoey A/P Clerk Figure 2: Spreadsheet used to conduct an audit of sales invoices

Answer 1

Q#	Cell	Formula
a	B5	Syntax =(lookup_value,Table_array, col_index_num, (range_lookup)} Formula =VLOOKUP(A5,$A$4:$B$9,2,False)
b	E26	=Countif($C$14:$C$22,D26)
c	F26	=sumif($C$14:$C$22,D26,$D$14:$D$22)
d	H14	=IF(D14*E14>G14,"too low", IF(D14*E14<G14,"too high,"correct"))

*$ sign is used for absolute referencing i.e., to fix the cells rows and columns