Bike Forums > Foo > Excel Spreadsheet Experts
 Register All Albums Elite Membership Forum Rules Calendar Search Today's Posts Mark Forums Read

 Foo Off-Topic chit chat with no general subject.

 12-08-07, 07:51 PM #1 reich17 Thread Starter     Join Date: Jan 2005 Bikes: Posts: 414 Mentioned: 0 Post(s) Tagged: 0 Thread(s) Quoted: 0 Post(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.
 12-08-07, 07:52 PM #2 nycphotography NYC     Join Date: Sep 2006 Bikes: Posts: 3,335 Mentioned: 15 Post(s) Tagged: 0 Thread(s) Quoted: 867 Post(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)
12-08-07, 09:13 PM   #3
reich17

Join Date: Jan 2005
Bikes:
Posts: 414
Mentioned: 0 Post(s)
Quoted: 0 Post(s)
Thanks, you saved an under-educated individual about half a day's trial and error.

Quote:
 Originally Posted by nycphotography 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)

 12-09-07, 09:14 AM #4 nycphotography NYC     Join Date: Sep 2006 Bikes: Posts: 3,335 Mentioned: 15 Post(s) Tagged: 0 Thread(s) Quoted: 867 Post(s) Actually, 1 or greater requires the >= operator in the IF test in B1. Sorry, I missed that the first time.