{"id":8429,"date":"2023-03-06T03:03:56","date_gmt":"2023-03-06T03:03:56","guid":{"rendered":"https:\/\/www.goodacademic.com\/blog\/questions\/excel-spreadsheet-involving-vlookup-conditional-formatting-countif-etc\/"},"modified":"2023-03-06T03:03:56","modified_gmt":"2023-03-06T03:03:56","slug":"excel-spreadsheet-involving-vlookup-conditional-formatting-countif-etc","status":"publish","type":"questions","link":"https:\/\/www.goodacademic.com\/blog\/questions\/excel-spreadsheet-involving-vlookup-conditional-formatting-countif-etc\/","title":{"rendered":"Excel Spreadsheet Involving VLookup, Conditional Formatting, CountIF, etc"},"content":{"rendered":"<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2. In POS , Create a Pivot Table (no &nbsp;&nbsp;chart) (starting in cell M13) detailing the KY Stores &amp; the total Qty &nbsp;&nbsp;of each item sold &nbsp;&nbsp;3. Note only: At this point you &nbsp;&nbsp;have a table detailing the qty of each item sold in KY over a 21 day period. &nbsp;&nbsp;4. Note only: The Sales data you &nbsp;&nbsp;have been given covers 21 days. We &nbsp;&nbsp;will assume these stores are open 365 days per year. &nbsp;&nbsp;5. On Turns &amp; Days populate the Sold Qty for each item &nbsp;&nbsp;6. Using On Hand Inventory for Kentucky, determine the Inventory Turnover &nbsp;&nbsp;Ratio and Days on Hand for each item. &nbsp;&nbsp;7. Sort by Psku &nbsp;&nbsp;8. Insert a column in B titles Description and populate it with the &nbsp;&nbsp;product Descriptions. &nbsp;&nbsp;9. Conditionally format the Days column to items with &nbsp;&nbsp;&nbsp;less than 7 days on hand &nbsp;&nbsp;= yellow &nbsp;&nbsp;&nbsp;between 7 and 42 days on &nbsp;&nbsp;hand = green &nbsp;&nbsp;&nbsp;more than 42 days on hand &nbsp;&nbsp;= red &nbsp;&nbsp;&nbsp;(Sample output from Turns &amp; Days is below) &nbsp;&nbsp;Copy &amp; Paste Values of the entire &nbsp;&nbsp;Turns &amp; Days page onto itself to &nbsp;&nbsp;remove all formulas and lookups &nbsp;&nbsp;&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2. In POS , Create a Pivot Table (no &nbsp;&nbsp;chart) (starting in cell M13) detailing the KY Stores &amp; the total Qty &nbsp;&nbsp;of each item sold &nbsp;&nbsp;3. Note only: At this point you &nbsp;&nbsp;have a table detailing the qty of each item sold in KY over a 21 day period. &nbsp;&nbsp;4. Note only: The Sales [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","template":"","meta":[],"disciplines":[632],"paper_types":[],"tagged":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions\/8429"}],"collection":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions"}],"about":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/types\/questions"}],"author":[{"embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/comments?post=8429"}],"version-history":[{"count":0,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions\/8429\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/media?parent=8429"}],"wp:term":[{"taxonomy":"disciplines","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/disciplines?post=8429"},{"taxonomy":"paper_types","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/paper_types?post=8429"},{"taxonomy":"tagged","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/tagged?post=8429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}