Question

Hello guys, i really need your help me solve problem with my assignment excel spreadsheet. how i put into value in column right (total : subtotal: HST: Amount Due: ) ? how can i solve problem like e.g =B2*B3 or =C4+ E4 or ISNUMBER? I am new to excel spreadsheet, i will learn from you, thank you so much

Problem 2: Invoice Figure 3. You may use the Item, Quantity and List_Price data as provided. • Discount Rate, Discount Threshold and HST_Rate amounts should be specified as shown and referenced in formulae so that any changes in those values will be automatically applied to the Sales Invoice calculations. Compute the Discount values so that e.g., 7.50% is displayed for items when Quantity exceeds the Discount Threshold but remain blank otherwise (not zeros). Sale_Price = the List_Price except when discount applys. Total = the item total price for the quantity ordered; Subtotal = the total of all line item totals; HST = the specified rate applied to the Subtotal Amount_Due = the sum of Subtotal and HST • Hint: Key functions to use: IF; ISNUMBER; Example: C D E F G B Sales Invoice List Price Discount Item Quantity 50 7.50% Printer Ink Paper Pack File Folders USB Sticks 24 Sale Price Total /Unit 36.07 1803.29 9.29|| 222.96 25.89|| 2589.08 12.99|506.61 /unit 38.99) 9.29 27.99 12.99 7.50% 100 39 Subtotal 5121.93 HST 665.85 Discount Rate 7.50% Discount_Threshold 50 HST_Rate 13% Amount Due | 5787.78 Figure 3

Answer 1

X B2 item А В C Е G Н sales invoice 1 list price sales price/ RESE quantity discount total item /unit 2 unit printer ink paper pack file folders 38.99 0.075 50 36.06575 1,803.29 9.29 4 24 9.29 0 222.96 2,589.08 5 100 27.99 0.075 25.89075 USB sticks 12.99 6 39 12.99 0 506.61 7 suntotal 5,121.93 HST 10 665.85 11 amount due 12 5,787.78 13 . st LO LO co a

for formulas and calculations, refer to the image below -

item A D E F G Н sales invoice 1 sales price unit quantity list price/ unit discount total item IF(C3<50,0,7.5%) D3*(1-E3) IF(C4<50,0,7.5%) | =D4*(1-E4) |=IF(C5<50,0,7.5%) D5*(1-E5) =IF(C6<50,0,7.5%) =D6*(1-E6) printer ink |раper pack file folders 38.99 -C3*F3 50 3 -C4*F4 24 4 9.29 -C5*F5 100 27.99 5 USB sticks -C6*F6 6 39 12.99 7 8 suntotal =SUBTOTAL (9,G3:G7) -G9*13% HST 10 11 -SUM(G9:G10) amount due 12 13 .. 82 LO LO o

In case you have any query, kindly ask in comments.