+ Reply to Thread
Results 1 to 15 of 15

Calculating Average and STDV.S with between range of dates and ignoring blanks cells

  1. #1
    Registered User
    Join Date
    04-20-2017
    Location
    Tel Aviv, Israel
    MS-Off Ver
    professional plus 2013
    Posts
    7

    Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Hi all ,

    I have a data set which contain asssetsID dates and returns as values.
    I would like to apply an average and STDV for a range of dates.
    I know that a regular average ignoring blank cells, however when I apply this formula :=AVERAGE(IF((Sheet1!$A$3:$A$308>$A2)*(Sheet1!$A$3:$A$308<=$A3),Sheet1!B$3:B$308))*60 it doesn't ignore blank cells.
    when I compare the results between a simple average and the formula above- I find several differences

    In following file, I attached the data in sheet 1, and an example of the calculation in Sheet2.
    I encounter a challenge because I would like to average/STDv.s cells without blank that meet the dates criteria.

    Anyone has an idea for the best approach for this task? I will be grateful.

    Thanks for your help ~!

    Nice day






    Thank you !
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,345

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Try this:

    =AVERAGE(IF((Sheet1!$A$3:$A$308>$A2)*(Sheet1!$A$3:$A$308<=$A3)*(Sheet1!B$3:B$308<>""),Sheet1!B$3:B$308))*60
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-20-2017
    Location
    Tel Aviv, Israel
    MS-Off Ver
    professional plus 2013
    Posts
    7

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Thank you so much! much appreciated
    what about cells that contain 0 ? how will the formula above will take it into consideration ?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,345

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Like this:

    =AVERAGE(IF((Sheet1!$A$3:$A$308>$A2)*(Sheet1!$A$3:$A$308<=$A3)*(Sheet1!B$3:B$308<>"")*(Sheet1!B$3:B$308<>0),Sheet1!B$3:B$308))*60

  5. #5
    Registered User
    Join Date
    04-20-2017
    Location
    Tel Aviv, Israel
    MS-Off Ver
    professional plus 2013
    Posts
    7

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Thank, GREAT
    and ignoring #N/A ?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,345

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Are we there yet?

    =IFNA(AVERAGE(IF((Sheet1!$A$3:$A$308>$A2)*(Sheet1!$A$3:$A$308<=$A3)*(Sheet1!B$3:B$308<>"")*(Sheet1!B$3:B$308<>0),Sheet1!B$3:B$308))*60,"")

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  7. #7
    Registered User
    Join Date
    04-20-2017
    Location
    Tel Aviv, Israel
    MS-Off Ver
    professional plus 2013
    Posts
    7

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    hi thank you , I would like that excel will ignore NA for example : 10,10,10,10, NA - it will return 10 as average while ignoring the NA observation .
    I'VE tried the above formula it returns a blank cell.

    Thank you

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,345

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    OK. Try this (untested):

    =AVERAGE(IF((Sheet1!$A$3:$A$308>$A2)*(Sheet1!$A$3:$A$308<=$A3)*(Sheet1!B$3:B$308<>"")*(Sheet1!B$3:B$308<>0)*(ISNA(Sheet1!B$3:B$308)<>TRUE),Sheet1!B$3:B$308))*60

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    also this should work ...

    =AVERAGEIFS(A1:A5,B1:B5,1,A1:A5,"<>0")

    AVERAGEIFS ignores "NA" or TEXT

    =AVERAGEIFS(Sheet1!B$3:B$308,Sheet1!$A$3:$A$308,">" &$A2,Sheet1!$A$3:$A$308,"<=" &$A3,Sheet1!B$3:B$308,"<>0")*60
    Last edited by JohnTopley; 04-23-2017 at 07:59 AM.

  10. #10
    Registered User
    Join Date
    04-20-2017
    Location
    Tel Aviv, Israel
    MS-Off Ver
    professional plus 2013
    Posts
    7

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Hi all,

    Thank your help but still without success. I attached a data example please take a look at "858883 858899 858994 859028" in sheet 2.
    Under these columns there are NA cells that does not related to the first range which was calculated and still got NA// result???
    I am trying to calculate average and STDEV.S between a range of dates, while it has to ignore blank cells and cells with NA, I approach this challenge in many ways, so I will be grateful
    if one the experts here will assist me with that.

    Thank you so much, appreciated .
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Why insert "=N/A" ???? just leave blank

    =AVERAGEIFS(Sheet1!B$3:B$308,Sheet1!$A$3:$A$308,">" &$A2,Sheet1!$A$3:$A$308,"<=" &$A3)*60

    This provides the same values as row 3 AND will work with #N/A

  12. #12
    Registered User
    Join Date
    04-20-2017
    Location
    Tel Aviv, Israel
    MS-Off Ver
    professional plus 2013
    Posts
    7

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Hi,

    Thanks! seems to work fine with AVERAGE,
    What about calculating STDEV.S- how should I formulate the formula - there are no STDEV.S ifs in excel

    Thank you!

    Nice day

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    As I said earlier: remove the #N/A.

  14. #14
    Registered User
    Join Date
    04-20-2017
    Location
    Tel Aviv, Israel
    MS-Off Ver
    professional plus 2013
    Posts
    7

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    I cannot , I am working with a larger data set without the option to change NA cells
    If you have an idea, I will be glad to

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Calculating Average and STDV.S with between range of dates and ignoring blanks cells

    Why can't the N/A be changed???

+ 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. Calculating a Current Average ignoring cells with no data yet.
    By DentonHTHS in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2015, 03:13 PM
  2. Ignoring blanks when calculating SUM
    By AZTeacher in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-13-2014, 06:03 PM
  3. [SOLVED] Average last n numbers ignoring blanks
    By Cryptic in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-06-2013, 07:05 AM
  4. Average tabs ignoring blanks
    By jbwizoz in forum Excel General
    Replies: 3
    Last Post: 11-16-2011, 05:00 PM
  5. Average ignoring Blanks cells
    By Justinmih in forum Excel General
    Replies: 10
    Last Post: 09-30-2011, 06:21 PM
  6. Replies: 1
    Last Post: 01-25-2011, 10:37 PM
  7. [SOLVED] Conditional Average Ignoring Blanks
    By Dirk Friedrichs via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-06-2005, 11:06 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