+ Reply to Thread
Results 1 to 5 of 5

Need help with sum(range 1+ range 2) / count (range 1+ range 2) -countif(range 1+ range2)

  1. #1
    Registered User
    Join Date
    11-17-2014
    Location
    Arizona
    MS-Off Ver
    2007
    Posts
    23

    Need help with sum(range 1+ range 2) / count (range 1+ range 2) -countif(range 1+ range2)

    Hello,

    I am somewhat new to this, so I am lost as to why this doesn't work. I have the following data in an excel spreadsheet.

    Jan. Feb. mar. Apr. may. Jun. Mid-Year. jul. August. Sep. oct. nov. dec. year-end
    16.98%


    I need to fill in the Mid-year and year-end cells with the average of the previous 6 months and 12 months respectively. Mid-year would be the average of Jan thru Jun. Year-end should be the average of all of the months. I am using this formula-- =sum(A1:F1)/count(A1:F1)-countif(A1:F1,"0"))
    To obtain the average of the 6 months, but not count a blank space. THIS WORKS! However, when I add in the 2nd range, it no longer works. =sum(A1:F1, H1:M1)/count(A1:F1, H1:M1)-countif(A1:F1,H1:M1,"0"))
    I get only the average of the first six months.

    I want to get the average of the first 6 months (mid-year) and the average of the full 12 months, but not count the blank spaces. The blanks spaces are not really blank, they have formulas.

    I hope I have explained this well enough. Please ask questions if not.

    Thank you for any help your can provide.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help with sum(range 1+ range 2) / count (range 1+ range 2) -countif(range 1+ rang

    1 way would be to use the SUBTOTAL function (it has lots of options to it)...
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    Jan Feb Mar Apr May Jun Mid-Yr Jul Aug Sep Oct Nov Dec Yr-End
    2
    10
    20
    30
    40
    50
    60
    35
    70
    80
    90
    100
    110
    120
    65
    3
    Regular AVERAGE
    35
    65

    G2=SUBTOTAL(1,A2:F2)
    N2=SUBTOTAL(1,A2:M2)
    SUBTOTAL() ignores any other SUBTOTAL cells it finds

    As a x-check...
    G3=AVERAGE(A2:F2)
    N3=AVERAGE(A2:F2,H2:M2)

    Does this help?

    hmm wait, I need to read properly, you said you could have blank cells that contain formulas - will take another look
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Need help with sum(range 1+ range 2) / count (range 1+ range 2) -countif(range 1+ rang

    Using your method

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help with sum(range 1+ range 2) / count (range 1+ range 2) -countif(range 1+ rang

    Try this, using my range above...
    G2=AVERAGEIF(A2:F2,">0")
    N2=AVERAGEIFS(A2:M2,$A$1:$M$1,"<>mid-yr",A2:M2,">0")

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Need help with sum(range 1+ range 2) / count (range 1+ range 2) -countif(range 1+ rang

    I try to avoid splitting up monthly data.Avoid the complication with:
    - SheetB = Jan - Dec data in continuous columns A to L
    - formulas for average/sum/countif etc are straightforward without a column in middle to hurdle over
    - Sheet A looks like your current sheet but all values driven by formula based on SheetB values

+ 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. if positive number in one range countif specific value in another range
    By sandreli in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2016, 04:49 AM
  2. Replies: 1
    Last Post: 12-17-2015, 11:22 AM
  3. [SOLVED] Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repeat
    By cjtimmer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-30-2015, 06:27 PM
  4. [SOLVED] Count number of occurances below a range, within a range, and below a range
    By maacmaac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2013, 11:36 PM
  5. [SOLVED] Countif / Sumif Range in a 24 Hour Time Range
    By cwwazy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-16-2013, 04:18 PM
  6. send to range, popup box to input what the range should be each time/select range
    By lax2734 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2012, 01:37 AM
  7. Replies: 15
    Last Post: 06-27-2011, 08:58 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1