Formula

[Cozmo35]

I need a formula that will tell me the average price (from Column "Q") and omit any "$0.00" or Blanks.

Even better, a formula that will allow me to enter the amount that will be omitted. (Example: anything less than $5.02 will be omitted)

 

[c_m_s_jr]

Somthing like?

=SUMPRODUCT(--($Q$2:$Q$10<>""),--($Q$2:$Q$10>R1),$Q$2:$Q$10)/SUMPRODUCT(--($Q$2:$Q$10<>""),--($Q$2:$Q$10>R1))

Where R1 is the where you would enter the $5.02 or what ever to exlude anything less than it.

 

[taigovinda]

Maybe this?

Excel Workbook
	Q	R	S
1	$5.00	$8.33
2	$0.00	10	5
3		9.375	4
4	$10.00
5	$10.00
6	$10.00
7	$10.00
8	$10.00
9	$10.00
10	$10.00
Sheet1

Excel 2003

Cell Formulas
Range	Formula
R1	=AVERAGE(Q1:Q10)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


Note that these are numbers formatted as $, the dollar sign is not actually in the cell...

Edit: mine doesn't work if you ever use negative numbers in the criteria cell (S2 or R1), but the one in post #2 does. Also c m s jr's formula is more efficient even if it is less pretty and if he didn't bother to provide a screenshot.