Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, Filter????
Hello,
I'm currently using page 1 of a work book as a data base. Not certain thats the best idea because opening, saving and calculating have become slower as the information expands. This is not my real question, but any feed back will be welcome. So page 1 is the database, on page 2 some calculations. Would like to include matching cells from column H that meet criterian held in columns W, X, Y and Z (1 each). then I would need to add and average these cells. Is this possible to do in one step? Should I use a Index Match or would a filter be the way. Thanks, as always for the help! M.A.Tyler |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, Filter????
Try rephrasing your question in terms like this:
I need to average cells A1:A100 where the corresponding cells in B1:B100 = "this", C1:C100 = "this", D1:D100 = "this". Biff "M.A.Tyler" <Great Lakes State wrote in message ... Hello, I'm currently using page 1 of a work book as a data base. Not certain thats the best idea because opening, saving and calculating have become slower as the information expands. This is not my real question, but any feed back will be welcome. So page 1 is the database, on page 2 some calculations. Would like to include matching cells from column H that meet criterian held in columns W, X, Y and Z (1 each). then I would need to add and average these cells. Is this possible to do in one step? Should I use a Index Match or would a filter be the way. Thanks, as always for the help! M.A.Tyler |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, Filter????
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and Z2:Z65535="Green". Need to enter the formulas in Sheet2!S2:S100. "T. Valko" wrote: Try rephrasing your question in terms like this: I need to average cells A1:A100 where the corresponding cells in B1:B100 = "this", C1:C100 = "this", D1:D100 = "this". Biff "M.A.Tyler" <Great Lakes State wrote in message ... Hello, I'm currently using page 1 of a work book as a data base. Not certain thats the best idea because opening, saving and calculating have become slower as the information expands. This is not my real question, but any feed back will be welcome. So page 1 is the database, on page 2 some calculations. Would like to include matching cells from column H that meet criterian held in columns W, X, Y and Z (1 each). then I would need to add and average these cells. Is this possible to do in one step? Should I use a Index Match or would a filter be the way. Thanks, as always for the help! M.A.Tyler |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, Filter????
Are you really using the entire columns?
This formula will work but it will add to the calculation time that you already mentioned is an issue: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) ) Biff "M.A.Tyler" <Great Lakes State wrote in message ... Need to Average cells H2:H65535, where corresponding cells in W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and Z2:Z65535="Green". Need to enter the formulas in Sheet2!S2:S100. "T. Valko" wrote: Try rephrasing your question in terms like this: I need to average cells A1:A100 where the corresponding cells in B1:B100 = "this", C1:C100 = "this", D1:D100 = "this". Biff "M.A.Tyler" <Great Lakes State wrote in message ... Hello, I'm currently using page 1 of a work book as a data base. Not certain thats the best idea because opening, saving and calculating have become slower as the information expands. This is not my real question, but any feed back will be welcome. So page 1 is the database, on page 2 some calculations. Would like to include matching cells from column H that meet criterian held in columns W, X, Y and Z (1 each). then I would need to add and average these cells. Is this possible to do in one step? Should I use a Index Match or would a filter be the way. Thanks, as always for the help! M.A.Tyler |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, Filter????
The only reason I need to use the entire column is to update the average. If
it was possible to save the average and the count as to how many cells were used to compile it, I could delete sheet1! and start fresh each time. This would cut the rows down from 65535 to about 2500. I would certainly be interested if this were possible. That would help with the speed problem! Thank you for your help and the formula. Best regards, M.A.Tyler "T. Valko" wrote: Are you really using the entire columns? This formula will work but it will add to the calculation time that you already mentioned is an issue: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) ) Biff "M.A.Tyler" <Great Lakes State wrote in message ... Need to Average cells H2:H65535, where corresponding cells in W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and Z2:Z65535="Green". Need to enter the formulas in Sheet2!S2:S100. "T. Valko" wrote: Try rephrasing your question in terms like this: I need to average cells A1:A100 where the corresponding cells in B1:B100 = "this", C1:C100 = "this", D1:D100 = "this". Biff "M.A.Tyler" <Great Lakes State wrote in message ... Hello, I'm currently using page 1 of a work book as a data base. Not certain thats the best idea because opening, saving and calculating have become slower as the information expands. This is not my real question, but any feed back will be welcome. So page 1 is the database, on page 2 some calculations. Would like to include matching cells from column H that meet criterian held in columns W, X, Y and Z (1 each). then I would need to add and average these cells. Is this possible to do in one step? Should I use a Index Match or would a filter be the way. Thanks, as always for the help! M.A.Tyler |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, Filter????
It sounds like what you need to do is to use a dynamic range.
See this: http://contextures.com/xlNames01.html#Dynamic Biff "M.A.Tyler" <Great Lakes State wrote in message ... The only reason I need to use the entire column is to update the average. If it was possible to save the average and the count as to how many cells were used to compile it, I could delete sheet1! and start fresh each time. This would cut the rows down from 65535 to about 2500. I would certainly be interested if this were possible. That would help with the speed problem! Thank you for your help and the formula. Best regards, M.A.Tyler "T. Valko" wrote: Are you really using the entire columns? This formula will work but it will add to the calculation time that you already mentioned is an issue: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) ) Biff "M.A.Tyler" <Great Lakes State wrote in message ... Need to Average cells H2:H65535, where corresponding cells in W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and Z2:Z65535="Green". Need to enter the formulas in Sheet2!S2:S100. "T. Valko" wrote: Try rephrasing your question in terms like this: I need to average cells A1:A100 where the corresponding cells in B1:B100 = "this", C1:C100 = "this", D1:D100 = "this". Biff "M.A.Tyler" <Great Lakes State wrote in message ... Hello, I'm currently using page 1 of a work book as a data base. Not certain thats the best idea because opening, saving and calculating have become slower as the information expands. This is not my real question, but any feed back will be welcome. So page 1 is the database, on page 2 some calculations. Would like to include matching cells from column H that meet criterian held in columns W, X, Y and Z (1 each). then I would need to add and average these cells. Is this possible to do in one step? Should I use a Index Match or would a filter be the way. Thanks, as always for the help! M.A.Tyler |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
I need a specific value in auto filter to fill a combo box and than match the select value in 2 other columns. | Excel Worksheet Functions | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
advanced filter - can't match a long text cell | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |