Home 
Search 
Today's Posts 
#1




IF Statement with Average Function results in #Value!
I wrote an IF Statement that takes the Average of a group of cells based on a
certain condition. Here is the Formula: {=AVERAGE(IF(($A$30:$A$515=A1)*($H$30:$H$515=B1),$ Q$30:$Q$515))} When a add a second part to the IF statement, I receive the error message #Value! I am able to add a number, but not text. Example: {=AVERAGE(IF(($A$30:$A$515=A1)*($H$30:$H$515=B1),$ Q$30:$Q$515),"FALSE")} I want to add the "FALSE" LOGIC TO MY IF Statement. Ideas? 
#2




Hi!
I want to add the "FALSE" LOGIC TO MY IF Statement. Well, for starters you have the "FALSE" as an argument in the AVERAGE function. That's why you're getting #VALUE!. What do you want the FALSE logic to mean? If (A30:A515=A1 * H30:H515=B1) = 0, you'll get a #DIV/0! error. Is that what you want the FALSE logic to mean? If so, try this: (array) =IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE( IF ((A30:A515=A1)*(H30:H515=B1),Q30:Q515))) Biff Original Message I wrote an IF Statement that takes the Average of a group of cells based on a certain condition. Here is the Formula: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515))} When a add a second part to the IF statement, I receive the error message #Value! I am able to add a number, but not text. Example: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515),"FALSE")} I want to add the "FALSE" LOGIC TO MY IF Statement. Ideas? . 
#3




Biff,
I need to somehow make the cell =TEXT and not #DIV/0!. This cell is linked to another workbook where I take the average of mutiple workbook cells. If this cell = #div/0!, then the average cannot be taken. For Example, Workbook1 A1 =5 and Workbook2 A1=5 and Workbook3 =#div/0! then when I take the average of the three workbooks, I get #div/0! "Biff" wrote: Hi! I want to add the "FALSE" LOGIC TO MY IF Statement. Well, for starters you have the "FALSE" as an argument in the AVERAGE function. That's why you're getting #VALUE!. What do you want the FALSE logic to mean? If (A30:A515=A1 * H30:H515=B1) = 0, you'll get a #DIV/0! error. Is that what you want the FALSE logic to mean? If so, try this: (array) =IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE( IF ((A30:A515=A1)*(H30:H515=B1),Q30:Q515))) Biff Original Message I wrote an IF Statement that takes the Average of a group of cells based on a certain condition. Here is the Formula: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515))} When a add a second part to the IF statement, I receive the error message #Value! I am able to add a number, but not text. Example: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515),"FALSE")} I want to add the "FALSE" LOGIC TO MY IF Statement. Ideas? . 
#4




Hi!
Ok, the formula I suggested will take care of that but if you want to specifically test for any errors and trap them: =IF(ISERROR(AVERAGE(IF((A30:A515=A1)* (H30:H515=B1),Q30:Q515))),"FALSE",AVERAGE(IF((A30: A515=A1)* (H30:H515B1),Q30:Q515))) Biff Original Message Biff, I need to somehow make the cell =TEXT and not #DIV/0!. This cell is linked to another workbook where I take the average of mutiple workbook cells. If this cell = #div/0!, then the average cannot be taken. For Example, Workbook1 A1 =5 and Workbook2 A1=5 and Workbook3 =#div/0! then when I take the average of the three workbooks, I get #div/0! "Biff" wrote: Hi! I want to add the "FALSE" LOGIC TO MY IF Statement. Well, for starters you have the "FALSE" as an argument in the AVERAGE function. That's why you're getting #VALUE!. What do you want the FALSE logic to mean? If (A30:A515=A1 * H30:H515=B1) = 0, you'll get a #DIV/0! error. Is that what you want the FALSE logic to mean? If so, try this: (array) =IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE( IF ((A30:A515=A1)*(H30:H515=B1),Q30:Q515))) Biff Original Message I wrote an IF Statement that takes the Average of a group of cells based on a certain condition. Here is the Formula: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515))} When a add a second part to the IF statement, I receive the error message #Value! I am able to add a number, but not text. Example: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515),"FALSE")} I want to add the "FALSE" LOGIC TO MY IF Statement. Ideas? . . 
#5




When I apply this formula, if Q30:Q515 =FALSE, then I receive the #div/0! as
a value. =IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE( IF((A30:A515=A1)*(H30:H515=B1),Q30:Q515))) IF Q30:Q515 =FALSE and H30:515 =B1, "FALSE",AVERAGE(IF((A30:A515=A1)*(H30:H515=B1),Q30 :Q515))) I am not sure how to write this formula "Biff" wrote: Hi! Ok, the formula I suggested will take care of that but if you want to specifically test for any errors and trap them: =IF(ISERROR(AVERAGE(IF((A30:A515=A1)* (H30:H515=B1),Q30:Q515))),"FALSE",AVERAGE(IF((A30: A515=A1)* (H30:H515B1),Q30:Q515))) Biff Original Message Biff, I need to somehow make the cell =TEXT and not #DIV/0!. This cell is linked to another workbook where I take the average of mutiple workbook cells. If this cell = #div/0!, then the average cannot be taken. For Example, Workbook1 A1 =5 and Workbook2 A1=5 and Workbook3 =#div/0! then when I take the average of the three workbooks, I get #div/0! "Biff" wrote: Hi! I want to add the "FALSE" LOGIC TO MY IF Statement. Well, for starters you have the "FALSE" as an argument in the AVERAGE function. That's why you're getting #VALUE!. What do you want the FALSE logic to mean? If (A30:A515=A1 * H30:H515=B1) = 0, you'll get a #DIV/0! error. Is that what you want the FALSE logic to mean? If so, try this: (array) =IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE( IF ((A30:A515=A1)*(H30:H515=B1),Q30:Q515))) Biff Original Message I wrote an IF Statement that takes the Average of a group of cells based on a certain condition. Here is the Formula: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515))} When a add a second part to the IF statement, I receive the error message #Value! I am able to add a number, but not text. Example: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515),"FALSE")} I want to add the "FALSE" LOGIC TO MY IF Statement. Ideas? . . 
#6




Hi!
AVERAGE will exclude text. Both formulas I've suggested work for me. If you're able to, you can send me a copy of the file (if it's not too big 1mb) and I'll take a look. Biff is valko01 at comcast period net. Biff Original Message When I apply this formula, if Q30:Q515 =FALSE, then I receive the #div/0! as a value. =IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE (IF ((A30:A515=A1)*(H30:H515=B1),Q30:Q515))) IF Q30:Q515 =FALSE and H30:515 =B1, "FALSE",AVERAGE(IF((A30:A515=A1)*(H30:H515=B1),Q3 0:Q515))) I am not sure how to write this formula "Biff" wrote: Hi! Ok, the formula I suggested will take care of that but if you want to specifically test for any errors and trap them: =IF(ISERROR(AVERAGE(IF((A30:A515=A1)* (H30:H515=B1),Q30:Q515))),"FALSE",AVERAGE(IF ((A30:A515=A1)* (H30:H515B1),Q30:Q515))) Biff Original Message Biff, I need to somehow make the cell =TEXT and not #DIV/0!. This cell is linked to another workbook where I take the average of mutiple workbook cells. If this cell = #div/0!, then the average cannot be taken. For Example, Workbook1 A1 =5 and Workbook2 A1=5 and Workbook3 =#div/0! then when I take the average of the three workbooks, I get #div/0! "Biff" wrote: Hi! I want to add the "FALSE" LOGIC TO MY IF Statement. Well, for starters you have the "FALSE" as an argument in the AVERAGE function. That's why you're getting #VALUE!. What do you want the FALSE logic to mean? If (A30:A515=A1 * H30:H515=B1) = 0, you'll get a #DIV/0! error. Is that what you want the FALSE logic to mean? If so, try this: (array) =IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE( IF ((A30:A515=A1)*(H30:H515=B1),Q30:Q515))) Biff Original Message I wrote an IF Statement that takes the Average of a group of cells based on a certain condition. Here is the Formula: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515))} When a add a second part to the IF statement, I receive the error message #Value! I am able to add a number, but not text. Example: {=AVERAGE(IF(($A$30:$A$515=A1)* ($H$30:$H$515=B1),$Q$30:$Q$515),"FALSE")} I want to add the "FALSE" LOGIC TO MY IF Statement. Ideas? . . . 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Access Module coded converted to Excel Function  Excel Discussion (Misc queries)  
Function in XL or in VBA for XL that pulls numeric digits from a t  Excel Discussion (Misc queries)  
Excel function help facilities  Excel Discussion (Misc queries)  
I cant use englisch function names in a swedich version of excel  Excel Discussion (Misc queries)  
Combining SUM Function with Nested If Statement  Excel Discussion (Misc queries) 