| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I can't reference a cell as the criteria in the AVERAGEIFS functio
I'm trying to use the AVERAGEIFS function to average a range of numbers if
the numbers are greater than the value in one cell and less than the value in another cell. However when I try to reference specific cells as the criteria in the formula I get a #DIV/0! error. The formula reads: =AVERAGEIFS(B3:B15,B3:B15,"B18",B3:B15,"<B19") IN this case B3:B15 a 23 31 17 35 41 30 23 34 29 26 29 20 34 And B18 is 22 and B19 is 35 If I replace B18 with 22 and B19 with 35 in the forumla, i.e. =AVERAGEIFS(B3:B15,B3:B15,"22",B3:B15,"<35") it calculates correctly with no error. Why can't a refence those cells as the criteria??? Thanks in advance! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I can't reference a cell as the criteria in the AVERAGEIFS functio
You need to use
=AVERAGEIFS(B3:B15,B3:B15,B18,B3:B15,B19) and enter 22 and <19 in B18 and B19 respectively... "Evan" wrote: I'm trying to use the AVERAGEIFS function to average a range of numbers if the numbers are greater than the value in one cell and less than the value in another cell. However when I try to reference specific cells as the criteria in the formula I get a #DIV/0! error. The formula reads: =AVERAGEIFS(B3:B15,B3:B15,"B18",B3:B15,"<B19") IN this case B3:B15 a 23 31 17 35 41 30 23 34 29 26 29 20 34 And B18 is 22 and B19 is 35 If I replace B18 with 22 and B19 with 35 in the forumla, i.e. =AVERAGEIFS(B3:B15,B3:B15,"22",B3:B15,"<35") it calculates correctly with no error. Why can't a refence those cells as the criteria??? Thanks in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I can't reference a cell as the criteria in the AVERAGEIFS functio
Try it like this:
=AVERAGEIFS(B3:B15,B3:B15,""&B18,B3:B15,"<"&B19) -- Biff Microsoft Excel MVP "Evan" wrote in message ... I'm trying to use the AVERAGEIFS function to average a range of numbers if the numbers are greater than the value in one cell and less than the value in another cell. However when I try to reference specific cells as the criteria in the formula I get a #DIV/0! error. The formula reads: =AVERAGEIFS(B3:B15,B3:B15,"B18",B3:B15,"<B19") IN this case B3:B15 a 23 31 17 35 41 30 23 34 29 26 29 20 34 And B18 is 22 and B19 is 35 If I replace B18 with 22 and B19 with 35 in the forumla, i.e. =AVERAGEIFS(B3:B15,B3:B15,"22",B3:B15,"<35") it calculates correctly with no error. Why can't a refence those cells as the criteria??? Thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumifs will not work with a cell reference as criteria | Excel Discussion (Misc queries) | |||
| To criteria with Reference from Cell | Excel Worksheet Functions | |||
| Reference cell in one TAB from another using two criteria | Excel Worksheet Functions | |||
| Return cell reference instead of text from within lookup() functio | Excel Worksheet Functions | |||
| SUMPRODUCT Criteria Via Cell Reference?? | Excel Worksheet Functions | |||
Hybrid Mode
