Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF but not if 'X' IS NULL
Hi,
I have a list of vehicle types and a list of MPG figures. I want to do an average MPG for each vehicle type so I need to know not only how many of the vehicle type there are but also how many of that type of vehicle have an MPG figure with them. E.g. I have 132 Peugeot vehicles, if I total the MPG figures and divide by 132 that gives me the average but only if every one of the Peugeots has a figure for that month which they dont. For this reason I need to know how many of those 132 have a figure with them. As the Peugeots are in a list with other vehicle types I first need to identify Peugeot then identify that MPG is not null, then count those cells. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF but not if 'X' IS NULL
You can use an array-entered AVERAGE(IF(... formula to do this - give
details of the columns used for the vehicle name and for the mpg and I can give you the rest of it. Pete On Aug 1, 12:33*pm, Pyrite wrote: Hi, I have a list of vehicle types and a list of MPG figures. I want to do an average MPG for each vehicle type so I need to know not only how many of the vehicle type there are but also how many of that type of vehicle have an MPG figure with them. E.g. I have 132 Peugeot vehicles, if I total the MPG figures and divide by 132 that gives me the average but only if every one of the Peugeots has a figure for that month which they dont. For this reason I need to know how many of those 132 have a figure with them. As the Peugeots are in a list with other vehicle types I first need to identify Peugeot then identify that MPG is not null, then count those cells. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF but not if 'X' IS NULL
The vehicle type coloumn is labelled 'Vehicle Type' and is from F13 to F500.
MPG column is 'MPG' and is G13 to G500. Thanks for your help "Pete_UK" wrote: You can use an array-entered AVERAGE(IF(... formula to do this - give details of the columns used for the vehicle name and for the mpg and I can give you the rest of it. Pete On Aug 1, 12:33 pm, Pyrite wrote: Hi, I have a list of vehicle types and a list of MPG figures. I want to do an average MPG for each vehicle type so I need to know not only how many of the vehicle type there are but also how many of that type of vehicle have an MPG figure with them. E.g. I have 132 Peugeot vehicles, if I total the MPG figures and divide by 132 that gives me the average but only if every one of the Peugeots has a figure for that month which they dont. For this reason I need to know how many of those 132 have a figure with them. As the Peugeots are in a list with other vehicle types I first need to identify Peugeot then identify that MPG is not null, then count those cells. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF but not if 'X' IS NULL
Okay, well let's say then that you have a list of vehicle types
somewhere else, eg starting in X1 with "Peugeot". Put this array* formula in Y1 =AVERAGE(IF((F13:F500=X1)*(G13:G500<""),G13:G500) ) * As this is an array formula, then once you have typed it in you need to commit it using Ctrl-Shift-Enter (CSE) instead of the usual <Enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. If you need to amend/edit the formula you must use CSE again. If you have other vehicle types in cells below X1, then copy the formula down column Y as appropriate. If you want to know how many vehicles, the formula is very similar - just change AVERAGE to COUNT, and use CSE again. Hope this helps. Pete On Aug 1, 1:05*pm, Pyrite wrote: The vehicle type coloumn is labelled 'Vehicle Type' and is from F13 to F500. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF but not if 'X' IS NULL
That works splendidly thank you. I'm not sure how it works but a couple of
re-reads of the formula and I will figure it out and add it to my repatoire for future use. Thanks again "Pete_UK" wrote: Okay, well let's say then that you have a list of vehicle types somewhere else, eg starting in X1 with "Peugeot". Put this array* formula in Y1 =AVERAGE(IF((F13:F500=X1)*(G13:G500<""),G13:G500) ) * As this is an array formula, then once you have typed it in you need to commit it using Ctrl-Shift-Enter (CSE) instead of the usual <Enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. If you need to amend/edit the formula you must use CSE again. If you have other vehicle types in cells below X1, then copy the formula down column Y as appropriate. If you want to know how many vehicles, the formula is very similar - just change AVERAGE to COUNT, and use CSE again. Hope this helps. Pete On Aug 1, 1:05 pm, Pyrite wrote: The vehicle type coloumn is labelled 'Vehicle Type' and is from F13 to F500. MPG column is 'MPG' and is G13 to G500. Thanks for your help "Pete_UK" wrote: You can use an array-entered AVERAGE(IF(... formula to do this - give details of the columns used for the vehicle name and for the mpg and I can give you the rest of it. Pete On Aug 1, 12:33 pm, Pyrite wrote: Hi, I have a list of vehicle types and a list of MPG figures. I want to do an average MPG for each vehicle type so I need to know not only how many of the vehicle type there are but also how many of that type of vehicle have an MPG figure with them. E.g. I have 132 Peugeot vehicles, if I total the MPG figures and divide by 132 that gives me the average but only if every one of the Peugeots has a figure for that month which they dont. For this reason I need to know how many of those 132 have a figure with them. As the Peugeots are in a list with other vehicle types I first need to identify Peugeot then identify that MPG is not null, then count those cells.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignor null in count | Excel Worksheet Functions | |||
COUNTIF says Null = Blank but Blank < Null | Excel Worksheet Functions | |||
Truly Null | Excel Worksheet Functions | |||
cell value based on null/not null in another cell | Excel Worksheet Functions | |||
Count number of days between dates BUT IF null to current date | Excel Worksheet Functions |