Client Database New Gift? Status Clients Total Jobs Jobs 6 24 6 18 9 Job Range 1 5 5 10 Status Bronze Silver Gold Platinum Client ID 10130 10195 10315 10540 10639 10679 10932 11230 11280 11325 20 12 1/1/20 1/1/14 1 5 New Start Date Loyalty Date New Starters Free Gifts Total Catering Jobs 1 10 258 11344 2 12 6 2 3 4 3 11365 11584 11646 11762 11854 11958 12136 12141 12345 12443 12714 12802 12B0B 12811 12638 13063 13301 13824 13875 14051 14504 14515 15329 15458 15627 15928 15957 Organisation Verisize LACNE First Finance Colot CTX Steps IT Training StepAhead Duet Axell Group Byte Size Data Pro Sys PicSure Ripple Com Pink Cloud Networks Cyber Data Processing TQ Processe CTX HectProof Collings University ASET PLC DENIL Zim Sales Intelligence Systems Respira Networks EYN Pilco Streambank XLAN Internet Exchange ICANT Wiz Labs Parmis Technologies Euro-M TQ Processes Oglev Zeonnect, Inc AHA Networks West Telco Ebony Telecom WWT Mojbal Fzig Fibre Chirch Technologies Ares IPI Bucharest Show Construction Epsilon Tech Tatson Qinisar UON Neta Assist Join Dale 28/11/15 29/1/13 18/6/14 7/8/15 17/9/17 14/1/14 S/S/20 8/2/16 23/7/20 17/7/16 3/10/17 7/11/19 22/4/14 16/10/14 8/3/19 17/8/17 11/5/16 1/12/19 21/5/18 1/7/18 20/1/15 15/11/13 15/1/20 14/12/19 24/9/13 7/11/16 14/5/18 18/6/19 6/7/15 17/11/13 27/2/16 30/4/15 14/4/15 10/10/19 9/8/18 9/6/18 20/10/19 5/7/20 1/9/14 5/9/18 3/8/18 6/8/20 13/10/16 16/4/18 29/7/14 16/1/20 14/7/18 12/7/18 29/9/14 Client Contact Email Brad Gorman bgorman@verisize.com.au Kanji Bhodia kbhodia@lone.com.au SeyedAllreza Vaziri svaziri@frifinance.com. Vasileios Glosas vgiotsas.color.com.au Geoffrey Huston chuston@cix.com.co Benedikt Stockebrand bstockebrand stepsittraining.com Petr Spadek plpaek@stepchead.com.au Tarek Fovod tfou odde.com Steven Leander slaanderaxellgroup.com.au Kevin Pillay kpillay@bytesize.com.au Miquel van Smoorenburg man smoorenburg@dataprosys.com.au Lukasz Janczura lianczuro@picsure.com.au Christian Kaufmann ckoutmann ripple.com.com.au Dennis Thomas dhomas pinkcloudnetworks.com.au Jonathan Freeman freeman cyberdataprocessing.com.au Mehmet TIK mlik qprocesse.com.au John Hill |hill@tx.com.ar Brian Nisbet brisbe@heciproof.com.au Sepehr Ashoori sashoorcolling university.com.au Jordi Palet Martinez palet martinez @asetplc.com.au Steve Balon sbalon denil.com.au Orlin Tenchev otenchev@zimales.com.au Musallam Alfarsi malforsi@intelligencesystems.com.au Sebastian Becker sbecker respiranetworks.com.au Ignas Bagdonas ibagdonas@eyn.com.cu Noora Balouma nbalouma@pilcostreambank.com.au Christoph Dietzel edietzel@xlaninternetexchange.com.au Shahab Vahabzadeh sechabzadeh icant.com.au Zubair Bin Abdul Kadar Shake shalk Quizlabs.com.au Hannaneh Hajiseyed ovadi hhajiseyediavodi@parmistechnologies.com Marius Gruen mgruen@euro-m.com.au Ondej Coletka ecoletka processes.com.au Ulf Kleber ukleber@oglev.com.au Thar Baranovsky iberenovsky@z connect, inc.com Kevin Pack kpockahanetworks.com.au Emest Byoruhongo ebyaruhanga westelco.com.au Karolina Hlobilov khlobilov@ebonytelecom.com.au Milad Afshari marshariwwi.com.au Miles McCrede mecredie@mojbal.com.au Ole Jacobsen olacobsen@fzigfibre.com.au Gery Van Emelen gran emelen@chirch technologies.com.au Sebastian Lohff slohff@ares.com.au lenus Sandu Isandpibucharest.com.co Knut A. Syed ksyed@shawconstruction.com.au Piotr Strzytawski pstrzyewskiepsilontech.com.au Seyed Ahmad Mousavi smousavitatsan.com.au Uta Meler-Hahn umeler-hohninisar.com.au Badar Al Mamari bal mamarluon.com.au Paul Thornton pthornton@netacissist.com.au 2 6 10 16 1 4 8 5 2 2 15 24 15 10 6 3 9 6 1 1 17091 7 2 2 1 8 6 17464 17769 18487 18536 19488 20626 20636 22216 22329 22740 6 1 6 3 7 C2 1 C3 N N C4 The formula in L15 is using the named range Jobs to add up the total number of catering jobs done for all clients. It is not returning the correct answer because the Named Range is not correct. Change the named range Jobs to include all cells from F6:F54. The value in L15 should now be correct. Convert the data in A5:154 to a table. Change the name of the table to ClientDB. In column G we want to identify all clients who joined in the last 9 months. The date 9 months ago has been calculated in L11. In G6 create a calculation to put Yes if the client start date was on or after the date shown in L11. If they joined before then, leave the cell empty (do not put a space). The formula should copy down automatically. We have decided to give a gift to thank customers for their loyalty. Create a calculation in H6 to return Gift if the client joined before the date shown in L12 or have purchased 15 or more jobs. If neither is true, leave the cell empty. The formula should copy down. C5 3 Client Database New Gift? Status Clients Total Jobs Jobs 6 24 6 18 9 Job Range 1 5 5 10 Status Bronze Silver Gold Platinum Client ID 10130 10195 10315 10540 10639 10679 10932 11230 11280 11325 20 12 1/1/20 1/1/14 1 5 New Start Date Loyalty Date New Starters Free Gifts Total Catering Jobs 1 10 258 11344 2 12 6 2 3 4 3 11365 11584 11646 11762 11854 11958 12136 12141 12345 12443 12714 12802 12B0B 12811 12638 13063 13301 13824 13875 14051 14504 14515 15329 15458 15627 15928 15957 Organisation Verisize LACNE First Finance Colot CTX Steps IT Training StepAhead Duet Axell Group Byte Size Data Pro Sys PicSure Ripple Com Pink Cloud Networks Cyber Data Processing TQ Processe CTX HectProof Collings University ASET PLC DENIL Zim Sales Intelligence Systems Respira Networks EYN Pilco Streambank XLAN Internet Exchange ICANT Wiz Labs Parmis Technologies Euro-M TQ Processes Oglev Zeonnect, Inc AHA Networks West Telco Ebony Telecom WWT Mojbal Fzig Fibre Chirch Technologies Ares IPI Bucharest Show Construction Epsilon Tech Tatson Qinisar UON Neta Assist Join Dale 28/11/15 29/1/13 18/6/14 7/8/15 17/9/17 14/1/14 S/S/20 8/2/16 23/7/20 17/7/16 3/10/17 7/11/19 22/4/14 16/10/14 8/3/19 17/8/17 11/5/16 1/12/19 21/5/18 1/7/18 20/1/15 15/11/13 15/1/20 14/12/19 24/9/13 7/11/16 14/5/18 18/6/19 6/7/15 17/11/13 27/2/16 30/4/15 14/4/15 10/10/19 9/8/18 9/6/18 20/10/19 5/7/20 1/9/14 5/9/18 3/8/18 6/8/20 13/10/16 16/4/18 29/7/14 16/1/20 14/7/18 12/7/18 29/9/14 Client Contact Email Brad Gorman bgorman@verisize.com.au Kanji Bhodia kbhodia@lone.com.au SeyedAllreza Vaziri svaziri@frifinance.com. Vasileios Glosas vgiotsas.color.com.au Geoffrey Huston chuston@cix.com.co Benedikt Stockebrand bstockebrand stepsittraining.com Petr Spadek plpaek@stepchead.com.au Tarek Fovod tfou odde.com Steven Leander slaanderaxellgroup.com.au Kevin Pillay kpillay@bytesize.com.au Miquel van Smoorenburg man smoorenburg@dataprosys.com.au Lukasz Janczura lianczuro@picsure.com.au Christian Kaufmann ckoutmann ripple.com.com.au Dennis Thomas dhomas pinkcloudnetworks.com.au Jonathan Freeman freeman cyberdataprocessing.com.au Mehmet TIK mlik qprocesse.com.au John Hill |hill@tx.com.ar Brian Nisbet brisbe@heciproof.com.au Sepehr Ashoori sashoorcolling university.com.au Jordi Palet Martinez palet martinez @asetplc.com.au Steve Balon sbalon denil.com.au Orlin Tenchev otenchev@zimales.com.au Musallam Alfarsi malforsi@intelligencesystems.com.au Sebastian Becker sbecker respiranetworks.com.au Ignas Bagdonas ibagdonas@eyn.com.cu Noora Balouma nbalouma@pilcostreambank.com.au Christoph Dietzel edietzel@xlaninternetexchange.com.au Shahab Vahabzadeh sechabzadeh icant.com.au Zubair Bin Abdul Kadar Shake shalk Quizlabs.com.au Hannaneh Hajiseyed ovadi hhajiseyediavodi@parmistechnologies.com Marius Gruen mgruen@euro-m.com.au Ondej Coletka ecoletka processes.com.au Ulf Kleber ukleber@oglev.com.au Thar Baranovsky iberenovsky@z connect, inc.com Kevin Pack kpockahanetworks.com.au Emest Byoruhongo ebyaruhanga westelco.com.au Karolina Hlobilov khlobilov@ebonytelecom.com.au Milad Afshari marshariwwi.com.au Miles McCrede mecredie@mojbal.com.au Ole Jacobsen olacobsen@fzigfibre.com.au Gery Van Emelen gran emelen@chirch technologies.com.au Sebastian Lohff slohff@ares.com.au lenus Sandu Isandpibucharest.com.co Knut A. Syed ksyed@shawconstruction.com.au Piotr Strzytawski pstrzyewskiepsilontech.com.au Seyed Ahmad Mousavi smousavitatsan.com.au Uta Meler-Hahn umeler-hohninisar.com.au Badar Al Mamari bal mamarluon.com.au Paul Thornton pthornton@netacissist.com.au 2 6 10 16 1 4 8 5 2 2 15 24 15 10 6 3 9 6 1 1 17091 7 2 2 1 8 6 17464 17769 18487 18536 19488 20626 20636 22216 22329 22740 6 1 6 3 7 C2 1 C3 N N C4 The formula in L15 is using the named range Jobs to add up the total number of catering jobs done for all clients. It is not returning the correct answer because the Named Range is not correct. Change the named range Jobs to include all cells from F6:F54. The value in L15 should now be correct. Convert the data in A5:154 to a table. Change the name of the table to ClientDB. In column G we want to identify all clients who joined in the last 9 months. The date 9 months ago has been calculated in L11. In G6 create a calculation to put Yes if the client start date was on or after the date shown in L11. If they joined before then, leave the cell empty (do not put a space). The formula should copy down automatically. We have decided to give a gift to thank customers for their loyalty. Create a calculation in H6 to return Gift if the client joined before the date shown in L12 or have purchased 15 or more jobs. If neither is true, leave the cell empty. The formula should copy down. C5 3