+ Reply to Thread
Results 1 to 6 of 6

Power Pivot or just regular Excel weighted average formula

  1. 10-14-2015, 10:47 AM #1
    stevemkiidub
    stevemkiidub is offline
    Registered User
    Join Date
    06-17-2015
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    25

    Power Pivot or just regular Excel weighted average formula

    Hi all

    OK I have a Power Pivot setup that gives me some basic sales information. I can't do a calculated field (greyed out) so I figured maybe just a regular formula will work. I don't think going into power pivot to do a DAX will work, I'm not sure how to anyways.

    39 40 41 Weighted Avg
    376 457 565 ??

    39, 40, 41 is the Week and the data is below it.

    So if I'm trying to estimate what I might sell in Week 42, a weighted average with more emphasis to the soonest week would be preferable. I used to remember how to do this but I'm stumped.

    Any thoughts?
    Reply With QuoteRegister To Reply
  2. 10-14-2015, 10:55 AM #2
    amotto11
    amotto11 is offline
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Power Pivot or just regular Excel weighted average formula

    if your data is column A, B, and C and row 2 it would just be =(A2*.2+B2*.3+C2*.5). If you have other weights then use them, if your weights are in the document then use sumproduct
    Regards,
    amotto

    If I helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 10-14-2015, 11:12 AM #3
    stevemkiidub
    stevemkiidub is offline
    Registered User
    Join Date
    06-17-2015
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    25

    Re: Power Pivot or just regular Excel weighted average formula

    Perfect thanks! Hey do you know an IF formula I can add to that so the formula is blank when no data is present in the pivot table? It just looks cleaner...

    Thanks again
    Reply With QuoteRegister To Reply
  4. 10-14-2015, 11:16 AM #4
    amotto11
    amotto11 is offline
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Power Pivot or just regular Excel weighted average formula

    You could use this =if(or(A2="",B2="",C2=""),"",A2*.2+B2*.3+C2*.5). Change it out for the sumproduct if needed, but make sure if you use sumproduct and your values don't add to 1, you will have to divide the sumproduct by the sum of your values.
    Reply With QuoteRegister To Reply
  5. 10-14-2015, 12:00 PM #5
    stevemkiidub
    stevemkiidub is offline
    Registered User
    Join Date
    06-17-2015
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    25

    Re: Power Pivot or just regular Excel weighted average formula

    Perfect worked great! Thanks!
    Reply With QuoteRegister To Reply
  6. 10-14-2015, 12:17 PM #6
    stevemkiidub
    stevemkiidub is offline
    Registered User
    Join Date
    06-17-2015
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    25

    Re: Power Pivot or just regular Excel weighted average formula

    Perfect worked great! Thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] weighted average in pivot
    By shani1975 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-28-2014, 12:35 PM
  2. weighted average % calculation in pivot table
    By maymano in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-28-2013, 10:33 PM
  3. Weighted Average in a Pivot Table
    By Maverick 3672 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-24-2013, 08:51 AM
  4. Weighted Average In Pivot
    By fredmeister in forum Excel General
    Replies: 0
    Last Post: 05-09-2012, 09:45 AM
  5. [SOLVED] Weighted Average in Pivot Table
    By sinspawn56 in forum Excel General
    Replies: 2
    Last Post: 04-05-2012, 05:26 PM
  6. Weighted Average in a Pivot Table
    By rmikulas in forum Excel General
    Replies: 2
    Last Post: 07-14-2010, 05:08 PM
  7. How do I weighted average using Pivot Tables?
    By JP in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-26-2006, 12:15 PM
  8. weighted average in pivot table
    By nasser in forum Excel General
    Replies: 3
    Last Post: 01-18-2006, 12:40 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1