(URGENT!!) Bus299: Excel Assignment 1. Calculate the total cost of ads for each invoice in the column "Total Cost of Ad
Posted: Sun May 08, 2022 9:58 am
(URGENT!!) Bus299: Excel
Assignment
1. Calculate the total cost of ads for each invoice in the
column "Total Cost of Ads".
2. Calculate the sales tax for each invoice based on the
information provided in cell I2 in column "Sales Tax", and
calculate the total bill in the column "Total Invoice Amount" by
adding total cost with sales tax for each client.
3. In "Shipping Date", calculate the shipment date as 6 months
from the date of invoice for each record. Hint: You must
use Date related functions here for full credit.
4. In "Invoice Group", based the table from O8 to P11 to
categorize each invoice to (Low, Middle, High) using VLookup
function. Hint: Use non-exact match (number range)
search.
Bonus Q1. MediaLoft keeps track of the email of each contact
person which is First Name of contact followed by "@medialoft.com".
For example, for the company Advertising Concepts, client contact
name is Ruby Esteban. Therefore, contact's email address should
[email protected]. HINT: First use LEFT function to
extract the first name for each client contact and then construct
the email address using CONCAT.
5. What is the maximum number of ads made for clients after
3/6/2009 who had ad media as pens, hats or T-shirts?
6. How many clients had purchased number of ads between 10 and
100 (both inclusive) after January 1, 2011?
7. What percentage of all invoices were produced for newspapers
or yellow pages ads with invoice dates between 1/31/2012 and
12/31/2012 (both dates inclusive) and whose number of ads was
between 2 and 10 (both inclusive)?
8. What is the total net cost per invoice generated by the
client name starting with word Village or ending with word Upstart?
HINT: Use wildcard.
9. Find the percent rank of the cost per ad (Column G) for
client whose invoice was created on 3/6/2009 and had newspaper as
media. Hint: Use DGET function.
AutoSave OFF Aca CA BUS299-01 E2_S22_RED Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments fx ΣΕ: |? V AL AL O Dix AB Calculate Now V Define Name Use in Formula Name Manager Create from Selection e, Trace Precedents a La Trace Dependents Show Error Watch FRemove Arrows Formulas Checking Window 圖蹈 Text Insert Function AutoSum Recently Financial Logical Used Date & Time Lookup & Math & Reference Trig More Functions Calculation Calculate Sheet Options D37 fx A B с D E F G H J к L L м N O P ۔ یہ دم 2 MediaLoft NYC Advertising Invoices 3 03 Q4 BQ1 Invoice 1 2 3 4 5 6 7 8 Invoice Amount Group $0 Small $1,000 Middle $2,000 Large Client Name 5 NYNEX 6 Advertising Concepts 7 WHAT 8 University Voice 9 Village Reader xwe 10 WHAT M 11 Mass Appeal, Inc. 46 12 Village Reader ras 13 Advertising Concepts 14 Young Upstart 15 Advertising Contepts 16 Advertising Concepts 17 1 Village Reader 18 Young Upstart 19 Advertising Concepts 20 WHAT 20 WHAT 21 University Voice 22 Advertising Concepts Client Contact Name Media Steve Yulich Yellow Pages Ruby Esteban Blow-in cards Emily Yu Radio spot Corey Lee Elishini Newspaper Elizabeth Lopez Newspaper Emily Yu Radio spot Gerard Smith Pens Elizabeth Lopez Newspaper Ruby Esteban Blow-in cards Robert Way Magazine Ruby Esteban Blow-in cards Ruby Esteban Billboard Elizabeth Lopez Newspaper Robert Way Magazine Ruby Esteban Billboard Emily Yu Radio spot 9 0.17 $ 366.73 $ Invoice Da 1/1/19 1/1/09 3/6/19 end 3/6/09 we 6/1/19 mo 7/7/19 mo 3/6/10 end 11/25/18 Wond 12/10/10 1/7/18 2/28/11 5/12/11 8/2519 9/27/11 9/28/19 12/1/11 4/12 4/1/12 7/6/12 M15/12 10/10/12 10/10/19 10/22/12 12/12/18 12/15/12 1/20/13 2/3/18 3/2/13 9/1/19 Sales Tax 8.55% Q1 Q2 Q2 Sales Tax Total Number of Total Cost of on Total Invoice Ads Cost Per Ads Cost Amount 2 $ 123.01 $ 246.02 $ 21.03 $ 267.05 230 $ 0.17 $ 39.56 $ 3.38 $ 42.94 25 $ 11.00 $ 275.00 $ 23.51 $ 298.51 d! 2 $ 23.91 $ 47.82 $ 4.09 $ 51.91 다 10! 00 Twee 8 $ etter 52.39 $ 419.12 $ 454.95 100 35.83 $ 10.16 wide ce 10 $ 11.00 $ 110.00 $ 9.41 $ 119.41 he " 330 $ 0.12 $ 40.59 $ 3.47 $ 1s 44.06 10 $ 52.39 $ 523.90 $ 44.79 $ 568.69 ei 275 $ 0.17 $ 47.30 $ 4.04 $ 51.34 12 $ 100.92 $ 1,211.04 $ 103.54 $ 1,314.58 275 $ 47.30 $ 4.04 $ 51.34 25 $ 102.00 $ 2,550.00 $ 218.03 $ 2,768.03 70 7 $ 52.39 $ 31.36 $ 31.36 398.09 15 $ 100.92 $ 1,513.80 $ 129.43 $ 1,643.23 20 $ 102.00 $ 102.00 $ 2,040.00 $ 174.42 $ 2,214.42 क 30 $ 11.00 $ 330.00 $ 28.22 28.22 $ 358.22 5 $ 52.39 $ 261.95 $ 22.40 $ 284.35 30 $ 27.00 $ $ 810.00 $ 69.26 69.26 $ 879 26 879.26 250 $ 0.12 $ 30.75 $ 2.63 $ 2.00 33.38 10 $ 123.01 $ 1,230.10 $ 105.17 $ 1,335.27 so 35 $ 27.00 $ 945.00 945.00 $ 80.80 $ 30$ 30 $ 27.00 $ 810.00 $ 69.26 $ 879.26 4 $ 4 23.91 $ 23.91 4 95.64 $ 8.18 103 82 $ 8.18 103.82 15 $ $ 11.00 $ 165.00 $ 14.11 $ 179.11 40 $ 27.00 $ 1,080.00 $ 92.34 $ 1,172.34 200 $ 5.67 $ 1,134.00 $ 96.96 $ 1,230.96 300 $ 7.20 $ 2.160.00 $ 184.68 $ 2,344.68 6 $ 123.01 $ 738.06 $ 63.10 $ 801.16 Shipping Invoice Date Group 7/1/19 Small 7/1/09 Small cam 9/6/19 Small enda 9/6/09 Small www.am 12/1/19 Small ammam 1/7/20 Small mam 9/6/10 Small en el 5/25/19 Small 0 am 6/10/11 Small 7/7/18 Middle 8/28/11 Small 11/12/11 Large 2/25/20 Small 3/2712 Middle 3/28/20 Large 6/1/12 Small 10/1/12 Small 1/6/13 Small 1/5/13 Small 1/15/13 Small 411013 Middle 4/10/20 Middle 4/22/13 Small 6/12/19 Small 6/15/13 Small 7/20/13 Middle 8/3/18 Middle 9/2/13 Large 3/1/20 Small Contact's Email Client First address Name [email protected] Steve [email protected] Ruby indo.com Rudy [email protected] Emily [email protected] Corey e [email protected] Elizabeth Menim [email protected] Emily median [email protected] Gerard Clicca [email protected] [email protected] Ruby [email protected] Robert [email protected] Ruby [email protected] Ruby [email protected] Elizabeth [email protected] Robert Puhu@medialoft com Ruby [email protected] Ruby [email protected] Emily [email protected] Corey [email protected] Ruby [email protected] Gerard [email protected] Steve Puhvemedieloftcom Bubu [email protected] Ruby [email protected] [email protected] Ruby [email protected] Corey [email protected] Emily [email protected] Ruby [email protected] Gerard [email protected] Elizabeth [email protected] Steve 3128120 Corey Lee 10 11 12 13 14 15 To 16 17 18 19 19 20 20 21 21 22 22 23 23 24 25 26 27 28 Newspaper 23 Mass Appeal, Inc. Ruby Esteban Subway Gerard Smith Steve Yulich 1,025 80 Recently Used 24 NYNEX 25 Advertising Concepts 26 Advertising Concepts 27 University Voice 28 WHAT 29 Advertising Concepts 30 Mass Appeal, Inc. 31 Village Reader 32 NYNEX so 33 34 35 36 37 Pens Yellow Pages Ruby Esteban Subway Ruby Esteban Subway Corey Lee Corey Lee Newspaper Emily Yu Radio spot Ruby Esteban Subway Gerard Smith T-Shirts Elizabeth Lopez Hats Steve Yulich Yellow Pages Abril del Cielo Pineda-Sanchez MediaLoft Queries LOOKUP Dashboard Template Dashboard Creation Total Invoice Amount Number of Ads by Media + Ready 间 3 Ш 120%
AutoSave OFF ACO BUS299-01 E2_522_RED Q go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments IS fox Σ Eva V ? AL e V V Vfx A Calculate Now Define Name BC Use in Formula v Name Manager EO Create from Selection v E E., Trace Precedents a Trace Dependents LGƏ Show Error Watch FRemove Arrows Formulas Checking Window V Text Insert Function AutoSum Recently Financial Logical Used Date & Time Lookup & Math & & & Reference Trig More Functions Calculation Calculate Sheet Options A4 fx Client Name В B A с D E F G H 1 J к L M N O Р P د یہ دی 2 MediaLoft NYC Advertising Invoices 03 Q4 BQ1 Invoice Da 1/1/19 1/1/09 Invoice 1 2 3 4 5 6 7 8 Sales Tax 8.55% Q1 Q2 Q2 Sales Tax Total Number of Total Cost of on Total Invoice Ads Cost Per- Ads Cost - Amount 2 $ 123.01 $ 246.02 $ 21.03 $ 267.05 230 $ 0.17 $ 39.56 $ 3.38 $ 42.94 22:07 $ 298.51 New Name $ 51.91 ce $ 454.95 1 $ 119.41 Adverlnvoices $ 44.06 $ 568.69 Workbook $ 51.34 $ 1,314.58 $ 51.34 Newspaper Invoice Amount Group $0 Small $1,000 Middle $2,000 Large Name: 9 Scope: Comment: Shipping Invoice Date Group 7/1/19 Small 7/1/09 Small www.am 9/6/19 Small woman 9/6/09 Small www.nam 12/1/19 Small 1/7/20 Small 9/6/10 Small en 5/25/19 Small han 6/10/11 Small 7/7/18 Middle 8/28/11 Small 11/12/11 Large 2/25/20 Small 3/27H2 Middle 3/28/20 Large 6/1/12 Small 10/1/12 Small 1/6/13 Small 1/5/13 Small 1/15/13 Small $ 2,768.03 Client Contact Name Media Steve Yulich Yellow Pages Ruby Esteban Blow-in cards Emily Yu Radio spot Corey Lee Elizabeth Lopez Newspaper mi Emily Yu Radio spot Gerard Smith Pens Elizabeth Lopez Newspaper Ruby Esteban Die Blow-in cards Robert Way Magazine Ruby Esteban Blow-in cards Ruby Esteban Billboard Elizabeth Lopez Newspaper Robert Way Magazine Ruby Esteban Billboard Emily Yu Radio spot Corey Lee Newspaper Ruby Esteban Gerard Smith Pens Pens Steve Steve Yulich Yellow Pages Ruby Esteban Subway Ruby Esteban Subway Corey Lee Newspaper Emily Yu Radio spot Ruby Esteban Subway Gerard Smith T-Shirts Elizabeth Lopez Hats Steve Yulich Yellow Pages $ Client Name 5 NYNEX 6 Advertising Concepts 7 WHAT 8 University Voice 9 Village Reader 10 WHAT 11 Mass Appeal, Inc. 12 Village Reader 13 Advertising Concepts 14 Young Upstart 15 Advertising Contepts 16 Advertising Concepts 17 18 Young Upstart 1 Village Reader 19 Advertising Concepts 20 WHAT 21 University Voice 22 Advertising Concepts 23 Mass Appeal, Inc. 24 NYNEX 25 Advertising Concepts 26 Advertising Concepts 27 University Voice 28 WHAT 29 Advertising Concepts 30 Mass Appeal, Inc. 31 Village Reader 29 32 NYNEX 33 34 35 36 37 $ 398.09 $ 1,643.23 $ 2.2144 Contact's Email Client First address Name [email protected] Steve [email protected] Ruby indo.com Ruby [email protected] Emily Comedia.com [email protected] Corey . [email protected]. Elizabeth Menim [email protected] Emily dia Bandoleratu [email protected] Gerard [email protected] Buhusialam bu [email protected] Ruby [email protected] Robert [email protected] Ruby [email protected] Ruby Elizabeth@medialoft. Elizabeth [email protected] Robert [email protected] Ruby [email protected] Emily [email protected] Corey [email protected] Ruby [email protected] Gerard [email protected] Steve [email protected] Ruby [email protected] Ruby [email protected] Corey [email protected] Emily [email protected] Ruby [email protected] Gerard [email protected] Elizabeth [email protected] Steve 10 11 12 12 13 14 14 15 16 17 18 19 20 20 21 22 23 24 25 26 27 28 $ 358.22 284 25 $ 284.35 $ 879.26 $ 33.38 Subway Refers to: =MediaLoft!$A$4:$P$32 4/10 Middle $ 1,335.27 $ 1,025 80 $ 879.26 ¢ Cancel OK $ 103.82 $ 179.11 $ 1,172.34 5.67 $ 1,134.00 $ 96.96 $ 1,230.96 7.20 $ 2,160.00 $ 184.68 $ 2,344.68 123.01 $ 738.06 $ 63.10 $ 801.16 4/10/20 Middle 4/22/13 Small 4/22/13 Small 6/12/19 Small 6/15/13 Small 7/20/13 Middle 8/3/18 Middle 9/2/13 Large 3/1/20 Small 2/3/18 3/2/13 9/1/19 200 $ 300 $ 6 $ Abril del Cielo Pineda-Sanchez MediaLoft Queries LOOKUP Dashboard Template Dashboard Creation Total Invoice Amount Number of Ads by Media + Average: 10590.83783 Count: 414 Ready Sum: 2404120.186 间 3 Ш 120%
AutoSave OFF CA BUS299-01 E2_S22_RED Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments fx Σ V |? V Dix AB AL V O Calculate Now V Define Name Use in Formula Name Manager EO Create from Selection B e, Trace Precedents La Trace Dependents Show Error Watch FRemove Arrows Formulas Checking Window Text Insert Function AutoSum Recently Financial Logical Used Date & Time Lookup & Math & & Reference Trig More Functions Calculation Co Calculate Sheet Options KZ х fx A B C с D E F. G H T J к K L м. M N O P P 1 BUS 299-02, E2 WHITE , 2 3 You MUST set up all your criteria for following questions on this "Queries" sheet! Also, use the appropriate database functions for any credit. Provide your Excel formula/function in the box provided for each question For some queries, you might need to add more columns to complete the conditions required. 4 5 6 Q5. $ 7 8 330.00 What is the maximum number of ads made for clients after 3/6/2009 who had ad media as pens, hats or T-shirts? Client Contact Name Sales Tax Number of Cost Per Total Cost on Total Total Invoice Ads Ad of Ads Amount Shipping Date Contact's Email address Client Name Invoice Group Cost Invoice ID 9 10 11 12 13 Media Pens Hats T-shirts Invoice Date >=3/6/2009 >=3/6/2009 >=3/6/2009 Q6. $ 14 15 24.00 How many clients had purchased number of ads between 10 and 100 (both inclusive) after January 1, 2011? Client Contact Name Shipping Date Contact's Email address Client Name Invoice ID Sales Tax Number of Cost Per Total Cost on Total Total Invoice Ads Ad of Ads Cost Amount >= 10 <=100 Invoice Group Media Invoice Date >1/1/2011 >1/1/2011 16 17 18 19 20 21 28 22 Q7. 0.00% What percentage of all invoices were produced for newspapers or yellow pages ads with invoice dates between 1/31/2012 and 12/31/2012 (both dates inclusive) and whose number of ads was between 23 Client Contact Name Contact's Email address Client Name Invoice ID Invoice Group 24 25 Number Number Cost Per Total Cost of Sales Tax on Total Invoice Shipping Invoice Date Invoice Date of Ads of Ads Ad Ads Total Cost Amount Date >=1/31/2012 <=12/31/2012 >=2 <=10 Media Newspaper MediaLoft Queries LOOKUP Dashboard Template Dashboard Creation Total Invoice Amount Number of Ads by Media + Ready 间 3 Ш 130%
AutoSave OFF CA BUS299-01 E2_S22_RED Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments fx ΣΗ: V V Dix AB AL V le Calculate Now V ? AutoSum Recently Financial Logical Used Define Name Use in Formula Name Manager Create from Selection B e, Trace Precedents La Trace Dependents Show Error Watch FRemove Arrows Formulas Checking Window Text Insert Function Date & Lookup & Lookup & Math & Time Reference Trig More Functions Calculation Co Calculate Sheet Options B22 fx =DCOUNT(MediaLoft, MediaLoft!A4,B24:P26)/COUNTA(MediaLoft!A5:A32) A B с C D E F F G H I J к L M м. N O P P TO 20 21 22 Q7. 0.00% What percentage of all invoices were produced for newspapers or yellow pages ads with invoice dates between 1/31/2012 and 12/31/2012 (both dates inclusive) and whose number of ads was between 23 Client Contact Name Invoice Group Contact's Email address Client Name Invoice ID 24 25 26 27 28 Number Number Cost Per Total Cost of Sales Tax on Total Invoice Shipping Media Invoice Date Invoice Date of Ads of Ads Ad Ads Total Cost Amount Date Newspaper >=1/31/2012 <=12/31/2012 >=2 <=10 Yellow Pages >=1/31/2012 <=12/31/2012 >=2 <=10 Q8. 29 30 What is the total net cost per invoice generated by the client name starting with word Village or ending with word Upstart? HINT: Use wildcard. Client Contact Name Sales Tax Number of Cost Per Total Cost on Total Total Invoice Ads Ad of Ads Cost Amount Shipping Date Contact's Email address Invoice ID Media Invoice Group Invoice Date 31 32 33 34 35 36 Client Name Village *Upstart Q9. 40.70% Find the percent rank of the cost per ad (Column G) for client whose invoice was created on 3/6/2009 and had newspaper as media. Hint: Use DGET function. 37 38 Client Contact Name Sales Tax Number of Cost Per Total Cost on Total Total Invoice Ads Ad of Ads Cost Amount Shipping Date Invoice Group Contact's Email address Client Name Invoice ID Media Newspaper Invoice Date 3/6/2009 39 40 41 42 43 44 45 Abril del Cielo Pineda-Sanchez MediaLoft Queries LOOKUP Dashboard Template Dashboard Creation Total Invoice Amount Number of Ads by Media + Ready 圓 3 Ш 130%
Assignment
1. Calculate the total cost of ads for each invoice in the
column "Total Cost of Ads".
2. Calculate the sales tax for each invoice based on the
information provided in cell I2 in column "Sales Tax", and
calculate the total bill in the column "Total Invoice Amount" by
adding total cost with sales tax for each client.
3. In "Shipping Date", calculate the shipment date as 6 months
from the date of invoice for each record. Hint: You must
use Date related functions here for full credit.
4. In "Invoice Group", based the table from O8 to P11 to
categorize each invoice to (Low, Middle, High) using VLookup
function. Hint: Use non-exact match (number range)
search.
Bonus Q1. MediaLoft keeps track of the email of each contact
person which is First Name of contact followed by "@medialoft.com".
For example, for the company Advertising Concepts, client contact
name is Ruby Esteban. Therefore, contact's email address should
[email protected]. HINT: First use LEFT function to
extract the first name for each client contact and then construct
the email address using CONCAT.
- Urgent Bus299 Excel Assignment 1 Calculate The Total Cost Of Ads For Each Invoice In The Column Total Cost Of Ad 1 (254.9 KiB) Viewed 22 times
3/6/2009 who had ad media as pens, hats or T-shirts?
6. How many clients had purchased number of ads between 10 and
100 (both inclusive) after January 1, 2011?
7. What percentage of all invoices were produced for newspapers
or yellow pages ads with invoice dates between 1/31/2012 and
12/31/2012 (both dates inclusive) and whose number of ads was
between 2 and 10 (both inclusive)?
8. What is the total net cost per invoice generated by the
client name starting with word Village or ending with word Upstart?
HINT: Use wildcard.
9. Find the percent rank of the cost per ad (Column G) for
client whose invoice was created on 3/6/2009 and had newspaper as
media. Hint: Use DGET function.
- Urgent Bus299 Excel Assignment 1 Calculate The Total Cost Of Ads For Each Invoice In The Column Total Cost Of Ad 2 (171.18 KiB) Viewed 22 times
AutoSave OFF ACO BUS299-01 E2_522_RED Q go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments IS fox Σ Eva V ? AL e V V Vfx A Calculate Now Define Name BC Use in Formula v Name Manager EO Create from Selection v E E., Trace Precedents a Trace Dependents LGƏ Show Error Watch FRemove Arrows Formulas Checking Window V Text Insert Function AutoSum Recently Financial Logical Used Date & Time Lookup & Math & & & Reference Trig More Functions Calculation Calculate Sheet Options A4 fx Client Name В B A с D E F G H 1 J к L M N O Р P د یہ دی 2 MediaLoft NYC Advertising Invoices 03 Q4 BQ1 Invoice Da 1/1/19 1/1/09 Invoice 1 2 3 4 5 6 7 8 Sales Tax 8.55% Q1 Q2 Q2 Sales Tax Total Number of Total Cost of on Total Invoice Ads Cost Per- Ads Cost - Amount 2 $ 123.01 $ 246.02 $ 21.03 $ 267.05 230 $ 0.17 $ 39.56 $ 3.38 $ 42.94 22:07 $ 298.51 New Name $ 51.91 ce $ 454.95 1 $ 119.41 Adverlnvoices $ 44.06 $ 568.69 Workbook $ 51.34 $ 1,314.58 $ 51.34 Newspaper Invoice Amount Group $0 Small $1,000 Middle $2,000 Large Name: 9 Scope: Comment: Shipping Invoice Date Group 7/1/19 Small 7/1/09 Small www.am 9/6/19 Small woman 9/6/09 Small www.nam 12/1/19 Small 1/7/20 Small 9/6/10 Small en 5/25/19 Small han 6/10/11 Small 7/7/18 Middle 8/28/11 Small 11/12/11 Large 2/25/20 Small 3/27H2 Middle 3/28/20 Large 6/1/12 Small 10/1/12 Small 1/6/13 Small 1/5/13 Small 1/15/13 Small $ 2,768.03 Client Contact Name Media Steve Yulich Yellow Pages Ruby Esteban Blow-in cards Emily Yu Radio spot Corey Lee Elizabeth Lopez Newspaper mi Emily Yu Radio spot Gerard Smith Pens Elizabeth Lopez Newspaper Ruby Esteban Die Blow-in cards Robert Way Magazine Ruby Esteban Blow-in cards Ruby Esteban Billboard Elizabeth Lopez Newspaper Robert Way Magazine Ruby Esteban Billboard Emily Yu Radio spot Corey Lee Newspaper Ruby Esteban Gerard Smith Pens Pens Steve Steve Yulich Yellow Pages Ruby Esteban Subway Ruby Esteban Subway Corey Lee Newspaper Emily Yu Radio spot Ruby Esteban Subway Gerard Smith T-Shirts Elizabeth Lopez Hats Steve Yulich Yellow Pages $ Client Name 5 NYNEX 6 Advertising Concepts 7 WHAT 8 University Voice 9 Village Reader 10 WHAT 11 Mass Appeal, Inc. 12 Village Reader 13 Advertising Concepts 14 Young Upstart 15 Advertising Contepts 16 Advertising Concepts 17 18 Young Upstart 1 Village Reader 19 Advertising Concepts 20 WHAT 21 University Voice 22 Advertising Concepts 23 Mass Appeal, Inc. 24 NYNEX 25 Advertising Concepts 26 Advertising Concepts 27 University Voice 28 WHAT 29 Advertising Concepts 30 Mass Appeal, Inc. 31 Village Reader 29 32 NYNEX 33 34 35 36 37 $ 398.09 $ 1,643.23 $ 2.2144 Contact's Email Client First address Name [email protected] Steve [email protected] Ruby indo.com Ruby [email protected] Emily Comedia.com [email protected] Corey . [email protected]. Elizabeth Menim [email protected] Emily dia Bandoleratu [email protected] Gerard [email protected] Buhusialam bu [email protected] Ruby [email protected] Robert [email protected] Ruby [email protected] Ruby Elizabeth@medialoft. Elizabeth [email protected] Robert [email protected] Ruby [email protected] Emily [email protected] Corey [email protected] Ruby [email protected] Gerard [email protected] Steve [email protected] Ruby [email protected] Ruby [email protected] Corey [email protected] Emily [email protected] Ruby [email protected] Gerard [email protected] Elizabeth [email protected] Steve 10 11 12 12 13 14 14 15 16 17 18 19 20 20 21 22 23 24 25 26 27 28 $ 358.22 284 25 $ 284.35 $ 879.26 $ 33.38 Subway Refers to: =MediaLoft!$A$4:$P$32 4/10 Middle $ 1,335.27 $ 1,025 80 $ 879.26 ¢ Cancel OK $ 103.82 $ 179.11 $ 1,172.34 5.67 $ 1,134.00 $ 96.96 $ 1,230.96 7.20 $ 2,160.00 $ 184.68 $ 2,344.68 123.01 $ 738.06 $ 63.10 $ 801.16 4/10/20 Middle 4/22/13 Small 4/22/13 Small 6/12/19 Small 6/15/13 Small 7/20/13 Middle 8/3/18 Middle 9/2/13 Large 3/1/20 Small 2/3/18 3/2/13 9/1/19 200 $ 300 $ 6 $ Abril del Cielo Pineda-Sanchez MediaLoft Queries LOOKUP Dashboard Template Dashboard Creation Total Invoice Amount Number of Ads by Media + Average: 10590.83783 Count: 414 Ready Sum: 2404120.186 间 3 Ш 120%
AutoSave OFF CA BUS299-01 E2_S22_RED Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments fx Σ V |? V Dix AB AL V O Calculate Now V Define Name Use in Formula Name Manager EO Create from Selection B e, Trace Precedents La Trace Dependents Show Error Watch FRemove Arrows Formulas Checking Window Text Insert Function AutoSum Recently Financial Logical Used Date & Time Lookup & Math & & Reference Trig More Functions Calculation Co Calculate Sheet Options KZ х fx A B C с D E F. G H T J к K L м. M N O P P 1 BUS 299-02, E2 WHITE , 2 3 You MUST set up all your criteria for following questions on this "Queries" sheet! Also, use the appropriate database functions for any credit. Provide your Excel formula/function in the box provided for each question For some queries, you might need to add more columns to complete the conditions required. 4 5 6 Q5. $ 7 8 330.00 What is the maximum number of ads made for clients after 3/6/2009 who had ad media as pens, hats or T-shirts? Client Contact Name Sales Tax Number of Cost Per Total Cost on Total Total Invoice Ads Ad of Ads Amount Shipping Date Contact's Email address Client Name Invoice Group Cost Invoice ID 9 10 11 12 13 Media Pens Hats T-shirts Invoice Date >=3/6/2009 >=3/6/2009 >=3/6/2009 Q6. $ 14 15 24.00 How many clients had purchased number of ads between 10 and 100 (both inclusive) after January 1, 2011? Client Contact Name Shipping Date Contact's Email address Client Name Invoice ID Sales Tax Number of Cost Per Total Cost on Total Total Invoice Ads Ad of Ads Cost Amount >= 10 <=100 Invoice Group Media Invoice Date >1/1/2011 >1/1/2011 16 17 18 19 20 21 28 22 Q7. 0.00% What percentage of all invoices were produced for newspapers or yellow pages ads with invoice dates between 1/31/2012 and 12/31/2012 (both dates inclusive) and whose number of ads was between 23 Client Contact Name Contact's Email address Client Name Invoice ID Invoice Group 24 25 Number Number Cost Per Total Cost of Sales Tax on Total Invoice Shipping Invoice Date Invoice Date of Ads of Ads Ad Ads Total Cost Amount Date >=1/31/2012 <=12/31/2012 >=2 <=10 Media Newspaper MediaLoft Queries LOOKUP Dashboard Template Dashboard Creation Total Invoice Amount Number of Ads by Media + Ready 间 3 Ш 130%
AutoSave OFF CA BUS299-01 E2_S22_RED Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments fx ΣΗ: V V Dix AB AL V le Calculate Now V ? AutoSum Recently Financial Logical Used Define Name Use in Formula Name Manager Create from Selection B e, Trace Precedents La Trace Dependents Show Error Watch FRemove Arrows Formulas Checking Window Text Insert Function Date & Lookup & Lookup & Math & Time Reference Trig More Functions Calculation Co Calculate Sheet Options B22 fx =DCOUNT(MediaLoft, MediaLoft!A4,B24:P26)/COUNTA(MediaLoft!A5:A32) A B с C D E F F G H I J к L M м. N O P P TO 20 21 22 Q7. 0.00% What percentage of all invoices were produced for newspapers or yellow pages ads with invoice dates between 1/31/2012 and 12/31/2012 (both dates inclusive) and whose number of ads was between 23 Client Contact Name Invoice Group Contact's Email address Client Name Invoice ID 24 25 26 27 28 Number Number Cost Per Total Cost of Sales Tax on Total Invoice Shipping Media Invoice Date Invoice Date of Ads of Ads Ad Ads Total Cost Amount Date Newspaper >=1/31/2012 <=12/31/2012 >=2 <=10 Yellow Pages >=1/31/2012 <=12/31/2012 >=2 <=10 Q8. 29 30 What is the total net cost per invoice generated by the client name starting with word Village or ending with word Upstart? HINT: Use wildcard. Client Contact Name Sales Tax Number of Cost Per Total Cost on Total Total Invoice Ads Ad of Ads Cost Amount Shipping Date Contact's Email address Invoice ID Media Invoice Group Invoice Date 31 32 33 34 35 36 Client Name Village *Upstart Q9. 40.70% Find the percent rank of the cost per ad (Column G) for client whose invoice was created on 3/6/2009 and had newspaper as media. Hint: Use DGET function. 37 38 Client Contact Name Sales Tax Number of Cost Per Total Cost on Total Total Invoice Ads Ad of Ads Cost Amount Shipping Date Invoice Group Contact's Email address Client Name Invoice ID Media Newspaper Invoice Date 3/6/2009 39 40 41 42 43 44 45 Abril del Cielo Pineda-Sanchez MediaLoft Queries LOOKUP Dashboard Template Dashboard Creation Total Invoice Amount Number of Ads by Media + Ready 圓 3 Ш 130%