+ Reply to Thread
Results 1 to 2 of 2

how can i find the average of absolute values of a range of cells where the range varies?

  1. #1
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    343

    how can i find the average of absolute values of a range of cells where the range varies?

    I have a column that varies from 1 to 24 rows. I need to find the average of the values, looking at their absolute value.

    I can use this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    but if the data only goes to b25 or b24 or anything less than B26 then it will average in 0 to the formula. How do i check how low the column goes before calculating the abs average?

    I can use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to find the last row but i dont know how to plug in the result from this formula into the range of the formula above.

    Edit: Forgot to mention the column contains formulas that either return a number or "" so it has to ignore the ""
    Last edited by kevinu; 01-12-2019 at 03:32 PM.

  2. #2
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    343

    Re: how can i find the average of absolute values of a range of cells where the range vari

    I figured out this formula but it still counts blank cells?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    B29 has this formula to count # of rows with data in it but it counts blank cells

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The blank cells have this formula in them so technically its not a blank cell it has "" in it.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Edit:

    Just figure out i was using countif when count would work so this formula works and makes the first formula work
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by kevinu; 01-12-2019 at 03:53 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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