+ Reply to Thread
Results 1 to 9 of 9

Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

  1. #1
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Leeds
    MS-Off Ver
    Microsoft Office 365
    Posts
    271

    Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    Good afternoon.

    I have got two separate worksheets from April 2019 to Sep 2019 and April 2020 to Sep 2020 for these 4 drugs: Rivaroxaban, Dapagliflozin, Empagliflozin, Ticagrelor. The system has the latest prescribing data until Sep 2020.
    This time it's a new indication for an existing product so we need to see the monthly increase in spend i.e. April 2019 to Sep 2019 vs April 2020 to Sep 2020 but can be done as one increase for the whole group rather than individually like the list above. I tried to do a pivot table but my Excel knowledge is very basic. I don't know how to present the data of these 4 drugs comparing their prescribing e.g Ticagrelor April 19 vs April 20. Please show me the steps so I can follow and improve. Thank you in advance.
    Jignesh.
    Attached Files Attached Files
    Last edited by Jigneshbharati; 12-04-2020 at 12:06 PM. Reason: attachment

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    I'm not really sure what you are trying to achieve. Do you want to end up with a graph for each drug which shows two series (one for each year), or show all spends for the four categories, or what?

    Pete

  3. #3
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Leeds
    MS-Off Ver
    Microsoft Office 365
    Posts
    271

    Re: Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    Thank you! Yes,to end up with a graph for each drug which shows two series (one for each year), and show all spends for the four categories as well. I need both. I am clinically sound but this presenting, interpreting and analysing data is very new to me.
    Last edited by Jigneshbharati; 12-04-2020 at 01:50 PM.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    See attached file, where I have added a new sheet called Graphs. Instead of 4 separate graphs, I've set up a DV drop-down in the merged cells G1:H1 where you can select a particular drug, then the graph automatically adjusts.

    Is that something you can work with?

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Leeds
    MS-Off Ver
    Microsoft Office 365
    Posts
    271

    Re: Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    Thank you so much! I am very grateful for the help I get here. Is there any way I can use just one pivot table that includes those 4 drugs and their month vs month (April 2019- Sep 2019 vs April 20 to Sep 20)?I created to pivot tablets for 2019 and 2020 and the layout is also not professional. I really appreciate your idea but I need a neater way to use just one pivot table to show the change in spending month wise, 2019 vs 2020. I am so sorry to ask you such basic questions.

  6. #6
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    Jigneshbharat,

    Attached suite may be of use.

    Pivot sheet shows the monthly spend on each product across the two years.

    Dashboard has a Table showing the total annual spends per Product and overall, under which a table shows the Variations in the 2020 budget compared to 2019, with CF set to Data Bars as a quick vieual guide.

    Under that the variances are shown as percentages, with CF set to "Star Rating" icon set.

    And two graphs in "test tube" format showing the percentage of the two overall budgets spent on each product (seemed an obvious way to show pharmacuetical products!)

    Hope this gives you some ideas.

    Ochimus
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Leeds
    MS-Off Ver
    Microsoft Office 365
    Posts
    271

    Re: Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    Thanks a lot. it's looks amazing. What does "CF" stand for? Would you please show me the steps show that I can replicate what you have done and I will be more confident If I can do it my self? I am so so.. grateful for your help. Have a nice weekend. Thank you!

  8. #8
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    Jigneshbharati,

    Many thanks for the compliment.

    "CF" is Conditional Formatting.
    The Pivot Table is made from your two annual worksheets.
    The graphs are "Stacked column" graphs set to Cylinder shapes.
    The Variance value table is a simple formula (2020 Month "a" value for produce "x" - 2019 value for same product in same month) then Formatted in CF with Data Bars in whichever colours you prefer.
    The Variance percentage table is a simple formula (2020 Month "a" value for produce "x"/ 2019 value for same product in same month) - 1. Formatted in CF with Star Rating icons.

    Fortunately the Web is packed with excellent guides on "how to", and I'm sure you'll get many other suggestions, but I suggest using these:

    For creating basic Pivot tables:
    https://www.exceldemy.com/excel-pivot-table-examples/

    To build the Table using two data sources (because you are creating a new worksheeet each year):
    https://www.techbout.com/pivot-table...%20items...%20

    Then for a range of really useful "advanced" techniques that make the Table even more creative:
    https://www.goskills.com/Excel/Resou...ble-techniques

    On making the Test Tube graph, this is the best explanation I've come across, and the output is really impressive!
    https://www.bing.com/videos/search?q...tail&FORM=VIRE

    You can also learn some amazing techniques from studying the solutions offered on here and other Excel forums - getting Excel to do things even MS never considered when they designed it!

    But above all, just practise until 'whatever' becomes second nature. Start your own "library" of "How to's", and you'll find it shaves hours off of some projects.

    Ochimus
    Last edited by Ochimus; 12-05-2020 at 02:03 PM.

  9. #9
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Leeds
    MS-Off Ver
    Microsoft Office 365
    Posts
    271

    Re: Presenting increase in spend for 4 drugs- month vs month from 2019 and 2020

    Thank you so much for your time. help and excellent answer!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] % of sales increase/decrease depending on new 2020 sales vs 2019 sales.
    By scubakerny in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-04-2020, 08:05 PM
  2. [SOLVED] Increase date by one month to end of month
    By T.Turner in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-12-2020, 07:33 AM
  3. [SOLVED] Correct exported date of 3/20/2019 to 3/1/2020
    By aquixano in forum Excel General
    Replies: 4
    Last Post: 09-20-2019, 11:32 AM
  4. Reflect TBF vacancies over 2018-2019/2019-2020 budget year
    By jennah63 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-28-2019, 07:32 PM
  5. Replies: 1
    Last Post: 07-06-2015, 04:43 PM
  6. Month over Month formula to show increase or decrease in rankings for SEO
    By lindsbay in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2014, 03:03 AM
  7. [SOLVED] Formula For Sum of Spend Per Month During a Start and End Date
    By t_man000 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-12-2013, 09:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1