Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup cells thAT are `NOT EQUAL` to a value / text
Hi , I`m currently using the following formula fine:
=AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89="W")*($AB $10:$AB$89-10),$AB$10:$AB$89)) This then selects only the values in the cells in collumn AB that satisfy the other criteria. Right, now I want to say do a similar search and average on the cells that match a specified conditions but in column N10:N89, I want to say "IS NOT EQUAL TO "Dog"" or whatever the text I want to be not included in the average. Sorry my description is probably rubbish. Hope someone gets the idea and can solve it. Thanks. RR1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup cells thAT are `NOT EQUAL` to a value / text
=AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89<"DOG")*( $AB$10:$AB$89-10),$AB$10:$AB$89)) means, if Hx is 0 and Nx is not Dog and ABx is more than -10, include the number in ABx in the average. "Romileyrunner1" wrote: Hi , I`m currently using the following formula fine: =AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89="W")*($AB $10:$AB$89-10),$AB$10:$AB$89)) This then selects only the values in the cells in collumn AB that satisfy the other criteria. Right, now I want to say do a similar search and average on the cells that match a specified conditions but in column N10:N89, I want to say "IS NOT EQUAL TO "Dog"" or whatever the text I want to be not included in the average. Sorry my description is probably rubbish. Hope someone gets the idea and can solve it. Thanks. RR1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup cells thAT are `NOT EQUAL` to a value / text
Hi,
=AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89="W")*($AB $10:$AB$89-10)*($N$10:$N$89<"Dog"),$AB$10:$AB$89)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... Hi , I`m currently using the following formula fine: =AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89="W")*($AB $10:$AB$89-10),$AB$10:$AB$89)) This then selects only the values in the cells in collumn AB that satisfy the other criteria. Right, now I want to say do a similar search and average on the cells that match a specified conditions but in column N10:N89, I want to say "IS NOT EQUAL TO "Dog"" or whatever the text I want to be not included in the average. Sorry my description is probably rubbish. Hope someone gets the idea and can solve it. Thanks. RR1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup cells thAT are `NOT EQUAL` to a value / text
Thanks Ashish.
Works great! "Ashish Mathur" wrote: Hi, =AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89="W")*($AB $10:$AB$89-10)*($N$10:$N$89<"Dog"),$AB$10:$AB$89)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... Hi , I`m currently using the following formula fine: =AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89="W")*($AB $10:$AB$89-10),$AB$10:$AB$89)) This then selects only the values in the cells in collumn AB that satisfy the other criteria. Right, now I want to say do a similar search and average on the cells that match a specified conditions but in column N10:N89, I want to say "IS NOT EQUAL TO "Dog"" or whatever the text I want to be not included in the average. Sorry my description is probably rubbish. Hope someone gets the idea and can solve it. Thanks. RR1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup cells thAT are `NOT EQUAL` to a value / text
Great work SAM
Works really well. Clever chap. Thanks "Sam Wilson" wrote: =AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89<"DOG")*( $AB$10:$AB$89-10),$AB$10:$AB$89)) means, if Hx is 0 and Nx is not Dog and ABx is more than -10, include the number in ABx in the average. "Romileyrunner1" wrote: Hi , I`m currently using the following formula fine: =AVERAGE(IF(($H$10:$H$89=0)*($N$10:$N$89="W")*($AB $10:$AB$89-10),$AB$10:$AB$89)) This then selects only the values in the cells in collumn AB that satisfy the other criteria. Right, now I want to say do a similar search and average on the cells that match a specified conditions but in column N10:N89, I want to say "IS NOT EQUAL TO "Dog"" or whatever the text I want to be not included in the average. Sorry my description is probably rubbish. Hope someone gets the idea and can solve it. Thanks. RR1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup If Date is equal to the Day | Excel Discussion (Misc queries) | |||
Vlookup to Equal Zero? | Excel Discussion (Misc queries) | |||
Vlookup but also equal to and greater than? | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions |