Advertise on Bikeforums.net



User Tag List

Results 1 to 4 of 4
  1. #1
    reich17's Avatar
    Join Date
    Jan 2005
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Excel Spreadsheet Experts

    I want to know what percentage of the cells in a specific column have a value of 1 or greater. Blank cells must be ignored when calculating the average. Which formula would determine this for me? Many thanks.
    .

  2. #2
    NYC nycphotography's Avatar
    Join Date
    Sep 2006
    Posts
    1,917
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in B1 (for example)
    =if(a1>1,1,0)

    then copy down the column.

    then at the bottom
    =sum(b1:b99) / count(a1:a99)

    (count ignores blank cells)

  3. #3
    reich17's Avatar
    Join Date
    Jan 2005
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, you saved an under-educated individual about half a day's trial and error.

    Quote Originally Posted by nycphotography View Post
    in B1 (for example)
    =if(a1>1,1,0)

    then copy down the column.

    then at the bottom
    =sum(b1:b99) / count(a1:a99)

    (count ignores blank cells)
    .

  4. #4
    NYC nycphotography's Avatar
    Join Date
    Sep 2006
    Posts
    1,917
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, 1 or greater requires the >= operator in the IF test in B1.

    Sorry, I missed that the first time.

Posting Permissions

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