| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
@average function
Hello,
I have a worksheet that has the months of the year in one column and a dollar amount in the next column. At the bottom I have summed the dollar amount column. My question: What would the formula be if I wanted to average the dollars over the months that have a dollar amount greater than zero? Thank you very much for your help. Chazbri |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
@average function
=SUM((B1:B120)*(B1:B12))/SUM(--(B1:B120))
array-entered with <Ctrl<Shift<Enter. "Chazbri" wrote in message news:kvGci.165721$_c5.126005@attbi_s22... Hello, I have a worksheet that has the months of the year in one column and a dollar amount in the next column. At the bottom I have summed the dollar amount column. My question: What would the formula be if I wanted to average the dollars over the months that have a dollar amount greater than zero? Thank you very much for your help. Chazbri |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
@average function
With
B1:B10 containing numbers with some zeros and/or blanks This ARRAY FORMULA returns the average of the values greater than zero: C1: =AVERAGE(IF(B1:B100,B1:B10)) Note: For array formulas, [Ctrl] [Shift] and press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "Chazbri" wrote: Hello, I have a worksheet that has the months of the year in one column and a dollar amount in the next column. At the bottom I have summed the dollar amount column. My question: What would the formula be if I wanted to average the dollars over the months that have a dollar amount greater than zero? Thank you very much for your help. Chazbri |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
@average function
Assuming there are no negative numbers in the range:
=SUM(B1:B20)/COUNTIF(B1:B20,"0") Biff "Chazbri" wrote in message news:kvGci.165721$_c5.126005@attbi_s22... Hello, I have a worksheet that has the months of the year in one column and a dollar amount in the next column. At the bottom I have summed the dollar amount column. My question: What would the formula be if I wanted to average the dollars over the months that have a dollar amount greater than zero? Thank you very much for your help. Chazbri |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
@average function
Hi,
A few corrections and additional suggestions: You could use any of the following: =AVERAGEIF(B1:B12,"0") in 2007 =SUMIF(B1:B12,"0")/COUNTIF(B1:B12,"0") no array also Ron's and Nanavati work, but not the other one. -- Cheers, Shane Devenshire "T. Valko" wrote: Assuming there are no negative numbers in the range: =SUM(B1:B20)/COUNTIF(B1:B20,"0") Biff "Chazbri" wrote in message news:kvGci.165721$_c5.126005@attbi_s22... Hello, I have a worksheet that has the months of the year in one column and a dollar amount in the next column. At the bottom I have summed the dollar amount column. My question: What would the formula be if I wanted to average the dollars over the months that have a dollar amount greater than zero? Thank you very much for your help. Chazbri |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
@average function
also Ron's and Nanavati work, but not the other one.
I guess you're referring to mine as the other one? What about it doesn't work? Biff "ShaneDevenshire" wrote in message ... Hi, A few corrections and additional suggestions: You could use any of the following: =AVERAGEIF(B1:B12,"0") in 2007 =SUMIF(B1:B12,"0")/COUNTIF(B1:B12,"0") no array also Ron's and Nanavati work, but not the other one. -- Cheers, Shane Devenshire "T. Valko" wrote: Assuming there are no negative numbers in the range: =SUM(B1:B20)/COUNTIF(B1:B20,"0") Biff "Chazbri" wrote in message news:kvGci.165721$_c5.126005@attbi_s22... Hello, I have a worksheet that has the months of the year in one column and a dollar amount in the next column. At the bottom I have summed the dollar amount column. My question: What would the formula be if I wanted to average the dollars over the months that have a dollar amount greater than zero? Thank you very much for your help. Chazbri |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
@average function
Hi Biff
You did preface your formula, with Assuming there are no negative numbers in the range in which case it would be absolutely fine I think Shane was referring to the fact that the OP said average the dollars over the months that have a dollar amount greater than zero? I think that all other posters have implied from this that there may be negative numbers, but I agree that may not be what the OP was saying. I guess using SUMIF(B1:B20,"0") covers both scenarios. -- Regards Roger Govier "T. Valko" wrote in message ... also Ron's and Nanavati work, but not the other one. I guess you're referring to mine as the other one? What about it doesn't work? Biff "ShaneDevenshire" wrote in message ... Hi, A few corrections and additional suggestions: You could use any of the following: =AVERAGEIF(B1:B12,"0") in 2007 =SUMIF(B1:B12,"0")/COUNTIF(B1:B12,"0") no array also Ron's and Nanavati work, but not the other one. -- Cheers, Shane Devenshire "T. Valko" wrote: Assuming there are no negative numbers in the range: =SUM(B1:B20)/COUNTIF(B1:B20,"0") Biff "Chazbri" wrote in message news:kvGci.165721$_c5.126005@attbi_s22... Hello, I have a worksheet that has the months of the year in one column and a dollar amount in the next column. At the bottom I have summed the dollar amount column. My question: What would the formula be if I wanted to average the dollars over the months that have a dollar amount greater than zero? Thank you very much for your help. Chazbri |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
@average function
Yes Roger,
This is really a question of how we read the question and I assumed that 0 did not imply that all numbers were positive in the original data set. And I did notice the preface but I assumed that a different question was being asked. As we all know we are up against the wall to guess what questions often mean. Hopefully users can work out what it is they are asking and therefore which solutions meet their needs. Nothing wrong with SUM(...)/ if there are no negative values. -- Cheers, Shane Devenshire "Roger Govier" wrote: Hi Biff You did preface your formula, with Assuming there are no negative numbers in the range in which case it would be absolutely fine I think Shane was referring to the fact that the OP said average the dollars over the months that have a dollar amount greater than zero? I think that all other posters have implied from this that there may be negative numbers, but I agree that may not be what the OP was saying. I guess using SUMIF(B1:B20,"0") covers both scenarios. -- Regards Roger Govier "T. Valko" wrote in message ... also Ron's and Nanavati work, but not the other one. I guess you're referring to mine as the other one? What about it doesn't work? Biff "ShaneDevenshire" wrote in message ... Hi, A few corrections and additional suggestions: You could use any of the following: =AVERAGEIF(B1:B12,"0") in 2007 =SUMIF(B1:B12,"0")/COUNTIF(B1:B12,"0") no array also Ron's and Nanavati work, but not the other one. -- Cheers, Shane Devenshire "T. Valko" wrote: Assuming there are no negative numbers in the range: =SUM(B1:B20)/COUNTIF(B1:B20,"0") Biff "Chazbri" wrote in message news:kvGci.165721$_c5.126005@attbi_s22... Hello, I have a worksheet that has the months of the year in one column and a dollar amount in the next column. At the bottom I have summed the dollar amount column. My question: What would the formula be if I wanted to average the dollars over the months that have a dollar amount greater than zero? Thank you very much for your help. Chazbri |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| AVERAGE function | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
| Average Function help | Excel Worksheet Functions | |||
| How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Linear Mode
