Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula needed... IF, SUMIF, or SUM ???
Here is my data...
2008 B C January 1.76 3.95 February 2.70 3.36 March 2.22 3.02 April 2.55 2.81 May 3.11 3.19 June #DIV/0! I'm working on a semiannual performance. Basically I need to sum-up the data in column C for Jan, Feb, Mar and so on..and then divide this by 6. But I will not know the data of June until July. So far, I'm using the IF function but I still get the DIV#0! error... I need the formula to add up according to the total of months that I have... For instance, in March I will only need to add up to 3 data value set and divided by 3, but I don't want to change the formula everymonth. I want a formula that work for 6 months. I hope someone can help. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula needed... IF, SUMIF, or SUM ???
"carlairis" wrote: Here is my data... 2008 B C January 1.76 3.95 February 2.70 3.36 March 2.22 3.02 April 2.55 2.81 May 3.11 3.19 June #DIV/0! I'm working on a semiannual performance. Basically I need to sum-up the data in column C for Jan, Feb, Mar and so on..and then divide this by 6. But I will not know the data of June until July. So far, I'm using the IF function but I still get the DIV#0! error... I need the formula to add up according to the total of months that I have... For instance, in March I will only need to add up to 3 data value set and divided by 3, but I don't want to change the formula everymonth. I want a formula that work for 6 months. I hope someone can help. Thanks! You can write a UDF to ignore errors. Try this copied into the books vb module Function FlexAvg(data) As Double Dim count As Long, mySum As Double, c For Each c In data If IsError(c) Then mySum = mySum count = count ElseIf IsNumeric(c) And c 0 Then mySum = mySum + c count = count + 1 End If Next FlexAvg = mySum / count End Function Regards Peter |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula needed... IF, SUMIF, or SUM ???
Hi
I am presuming the data to be summed is in column B and the result is in column C. If different change the column references accordingly In C2 =IF(B2="","",SUM(OFFSET(B2,0,0,MIN(6,COUNT($B$1:B2 ))*-1,1))/MIN(6,COUNT($B$2:B2))) Copy down as far as required -- Regards Roger Govier "carlairis" wrote in message ... Here is my data... 2008 B C January 1.76 3.95 February 2.70 3.36 March 2.22 3.02 April 2.55 2.81 May 3.11 3.19 June #DIV/0! I'm working on a semiannual performance. Basically I need to sum-up the data in column C for Jan, Feb, Mar and so on..and then divide this by 6. But I will not know the data of June until July. So far, I'm using the IF function but I still get the DIV#0! error... I need the formula to add up according to the total of months that I have... For instance, in March I will only need to add up to 3 data value set and divided by 3, but I don't want to change the formula everymonth. I want a formula that work for 6 months. I hope someone can help. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula needed... IF, SUMIF, or SUM ???
Thank you for your reply, I think that my post was not clear enough,
I might have confuse you. I created a workbook with reference cells that have dependencies on other worksheets and/or workbook, until these other cells have a value placed in them my reference cell returns the #DIV/! error. Here is a brief example, book1, lets say have two sheets set up one named Sheet1: wkld (the source) and the other sheet2: PerfReview Jan-June. In sheet1 I have the following cells: (cell)month (a10)January 1.76 3.95 (a11)February 2.70 3.36 (a12)March 2.22` 3.02 (a13)April 2.55 2.81 (a14)May 3.11 3.19 (a15)June #DIV/0! I want the formula to calculate the average of the current data set for column C (Column B is something else), even if I have 3 to 5 #DIV/0! Error cell. Can that be possible?? I tried using AVERAGE fxn, but it's still giving me error. How can I tell the formula to ignore these? Thanks, again! "Roger Govier" wrote: Hi I am presuming the data to be summed is in column B and the result is in column C. If different change the column references accordingly In C2 =IF(B2="","",SUM(OFFSET(B2,0,0,MIN(6,COUNT($B$1:B2 ))*-1,1))/MIN(6,COUNT($B$2:B2))) Copy down as far as required -- Regards Roger Govier "carlairis" wrote in message ... Here is my data... 2008 B C January 1.76 3.95 February 2.70 3.36 March 2.22 3.02 April 2.55 2.81 May 3.11 3.19 June #DIV/0! I'm working on a semiannual performance. Basically I need to sum-up the data in column C for Jan, Feb, Mar and so on..and then divide this by 6. But I will not know the data of June until July. So far, I'm using the IF function but I still get the DIV#0! error... I need the formula to add up according to the total of months that I have... For instance, in March I will only need to add up to 3 data value set and divided by 3, but I don't want to change the formula everymonth. I want a formula that work for 6 months. I hope someone can help. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula needed... IF, SUMIF, or SUM ???
Hi
Because of the wrapping by the NG reader it is not easy to see the values you have. Where do you want the formula to exists? In column D? If so, just change the formula I gave you to refer to column C instead of Column B =IF(C2="","",SUM(OFFSET(C2,0,0,MIN(6,COUNT($C$2:C2 ))*-1,1))/MIN(6,COUNT($C$2:C2))) If you still can't resolve the problem, send me a copy of your workbook. To send direct use roger at technology4u dot co dot uk Change the at and dots to make a valid email address. -- Regards Roger Govier "carlairis" wrote in message ... Thank you for your reply, I think that my post was not clear enough, I might have confuse you. I created a workbook with reference cells that have dependencies on other worksheets and/or workbook, until these other cells have a value placed in them my reference cell returns the #DIV/! error. Here is a brief example, book1, lets say have two sheets set up one named Sheet1: wkld (the source) and the other sheet2: PerfReview Jan-June. In sheet1 I have the following cells: (cell)month (a10)January 1.76 3.95 (a11)February 2.70 3.36 (a12)March 2.22` 3.02 (a13)April 2.55 2.81 (a14)May 3.11 3.19 (a15)June #DIV/0! I want the formula to calculate the average of the current data set for column C (Column B is something else), even if I have 3 to 5 #DIV/0! Error cell. Can that be possible?? I tried using AVERAGE fxn, but it's still giving me error. How can I tell the formula to ignore these? Thanks, again! "Roger Govier" wrote: Hi I am presuming the data to be summed is in column B and the result is in column C. If different change the column references accordingly In C2 =IF(B2="","",SUM(OFFSET(B2,0,0,MIN(6,COUNT($B$1:B2 ))*-1,1))/MIN(6,COUNT($B$2:B2))) Copy down as far as required -- Regards Roger Govier "carlairis" wrote in message ... Here is my data... 2008 B C January 1.76 3.95 February 2.70 3.36 March 2.22 3.02 April 2.55 2.81 May 3.11 3.19 June #DIV/0! I'm working on a semiannual performance. Basically I need to sum-up the data in column C for Jan, Feb, Mar and so on..and then divide this by 6. But I will not know the data of June until July. So far, I'm using the IF function but I still get the DIV#0! error... I need the formula to add up according to the total of months that I have... For instance, in March I will only need to add up to 3 data value set and divided by 3, but I don't want to change the formula everymonth. I want a formula that work for 6 months. I hope someone can help. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula needed... IF, SUMIF, or SUM ???
Thanks for your reply... but i figure it out by using this..
=AVERAGEIF(C10:C15,"0",C10:C15) Basically, it ignores the #DIV/0! that I have reference from another worksheet. Your response was really impressive... "Billy Liddel" wrote: "carlairis" wrote: Here is my data... 2008 B C January 1.76 3.95 February 2.70 3.36 March 2.22 3.02 April 2.55 2.81 May 3.11 3.19 June #DIV/0! I'm working on a semiannual performance. Basically I need to sum-up the data in column C for Jan, Feb, Mar and so on..and then divide this by 6. But I will not know the data of June until July. So far, I'm using the IF function but I still get the DIV#0! error... I need the formula to add up according to the total of months that I have... For instance, in March I will only need to add up to 3 data value set and divided by 3, but I don't want to change the formula everymonth. I want a formula that work for 6 months. I hope someone can help. Thanks! You can write a UDF to ignore errors. Try this copied into the books vb module Function FlexAvg(data) As Double Dim count As Long, mySum As Double, c For Each c In data If IsError(c) Then mySum = mySum count = count ElseIf IsNumeric(c) And c 0 Then mySum = mySum + c count = count + 1 End If Next FlexAvg = mySum / count End Function Regards Peter |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula needed... IF, SUMIF, or SUM ???
I could not get your formula to work - I'm using xl2003, however this array
formula worked; enter it as Ctrl + Shift Enter =AVERAGE(IF(ISNUMBER(C2:C13),C2:C13)) Peter "carlairis" wrote: Thanks for your reply... but i figure it out by using this.. =AVERAGEIF(C10:C15,"0",C10:C15) Basically, it ignores the #DIV/0! that I have reference from another worksheet. Your response was really impressive... "Billy Liddel" wrote: "carlairis" wrote: Here is my data... 2008 B C January 1.76 3.95 February 2.70 3.36 March 2.22 3.02 April 2.55 2.81 May 3.11 3.19 June #DIV/0! I'm working on a semiannual performance. Basically I need to sum-up the data in column C for Jan, Feb, Mar and so on..and then divide this by 6. But I will not know the data of June until July. So far, I'm using the IF function but I still get the DIV#0! error... I need the formula to add up according to the total of months that I have... For instance, in March I will only need to add up to 3 data value set and divided by 3, but I don't want to change the formula everymonth. I want a formula that work for 6 months. I hope someone can help. Thanks! You can write a UDF to ignore errors. Try this copied into the books vb module Function FlexAvg(data) As Double Dim count As Long, mySum As Double, c For Each c In data If IsError(c) Then mySum = mySum count = count ElseIf IsNumeric(c) And c 0 Then mySum = mySum + c count = count + 1 End If Next FlexAvg = mySum / count End Function Regards Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF help needed | Excel Discussion (Misc queries) | |||
Help needed with a SUMIF(AND | Excel Discussion (Misc queries) | |||
SumIf help needed ( I think) | Excel Discussion (Misc queries) | |||
SumIf help needed plz... | Excel Worksheet Functions | |||
SUMIF help needed | Excel Worksheet Functions |