| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average with 2 criteria
i am trying to set up an average days for inventory based on two criteria.
i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
Try this
=AVERAGE(IF((I1:I200="Stock")*(K1:K200="x"),L1:L20 0)) Enter as an array by pressing CTRL+Shift+Enter NOT just Enter. If you do it correctly then Excel will put curly brackets around it {}. You can't type these yourself. Mike "hockeyb9" wrote: i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
What's not working? Though you have an unnecessary ( ), it seems to work
with your formula or either of the following: =SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x")) =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) Your sample data only has one match and therefore is divided by 1. HTH, Paul -- "hockeyb9" wrote in message ... i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
Hmmm,
You got his formula to work as posted? For me it produces a value error as does your second. Mike "PCLIVE" wrote: What's not working? Though you have an unnecessary ( ), it seems to work with your formula or either of the following: =SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x")) =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) Your sample data only has one match and therefore is divided by 1. HTH, Paul -- "hockeyb9" wrote in message ... i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
i tried this and got the dreaded #DIV/0!
i did enter as an array. any idea - in my real data i am matching to an actual cell. ie) instead of "stock" it's $c$5 so that i can have the same formula for multiple matches. thanks for your help mike. "Mike H" wrote: Try this =AVERAGE(IF((I1:I200="Stock")*(K1:K200="x"),L1:L20 0)) Enter as an array by pressing CTRL+Shift+Enter NOT just Enter. If you do it correctly then Excel will put curly brackets around it {}. You can't type these yourself. Mike "hockeyb9" wrote: i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
Hi,
If you got that then I suggest you check your data. It would happen if it couldn't find a single mtach in columns I & K or the numbers in column L aren't really numbers. The usual culprits are rogue spaces. Manually find a match and check all 3 pieces of data so you are 100% sure you have at least 1 match and DIV/0 should go away but you may still have a problem with other data. Mike "hockeyb9" wrote: i tried this and got the dreaded #DIV/0! i did enter as an array. any idea - in my real data i am matching to an actual cell. ie) instead of "stock" it's $c$5 so that i can have the same formula for multiple matches. thanks for your help mike. "Mike H" wrote: Try this =AVERAGE(IF((I1:I200="Stock")*(K1:K200="x"),L1:L20 0)) Enter as an array by pressing CTRL+Shift+Enter NOT just Enter. If you do it correctly then Excel will put curly brackets around it {}. You can't type these yourself. Mike "hockeyb9" wrote: i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
Actually yes. I don't know if something quirky is happening...but all four
formulas, including yours and the OPs, gives the same result. Should it not work? -- "Mike H" wrote in message ... Hmmm, You got his formula to work as posted? For me it produces a value error as does your second. Mike "PCLIVE" wrote: What's not working? Though you have an unnecessary ( ), it seems to work with your formula or either of the following: =SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x")) =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) Your sample data only has one match and therefore is divided by 1. HTH, Paul -- "hockeyb9" wrote in message ... i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
The OP's didn't for me. The reason is because I used the data layout as
supplied by the OP and with a header that formula will fail status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 Mike "PCLIVE" wrote: Actually yes. I don't know if something quirky is happening...but all four formulas, including yours and the OPs, gives the same result. Should it not work? -- "Mike H" wrote in message ... Hmmm, You got his formula to work as posted? For me it produces a value error as does your second. Mike "PCLIVE" wrote: What's not working? Though you have an unnecessary ( ), it seems to work with your formula or either of the following: =SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x")) =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) Your sample data only has one match and therefore is divided by 1. HTH, Paul -- "hockeyb9" wrote in message ... i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
hey guys, i have tried formula both ways.
i hvae rechecked the data to ensure matches - i actually copied the match cell from data. still getting the DIV/0! i really appreciate your help in trying to figure this out because i am stumped as to why it doesn't work. "PCLIVE" wrote: Actually yes. I don't know if something quirky is happening...but all four formulas, including yours and the OPs, gives the same result. Should it not work? -- "Mike H" wrote in message ... Hmmm, You got his formula to work as posted? For me it produces a value error as does your second. Mike "PCLIVE" wrote: What's not working? Though you have an unnecessary ( ), it seems to work with your formula or either of the following: =SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x")) =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) Your sample data only has one match and therefore is divided by 1. HTH, Paul -- "hockeyb9" wrote in message ... i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
You may have additional unseen spaces in your data that prevent a match from
being made. Try this: =AVERAGE(IF((TRIM(I1:I200)="Stock")*(K1:K200="x"), L1:L200)) committed with Ctrl+Shift+Enter (Mikes formula) of =SUMPRODUCT(--(TRIM($I$1:$I$200)="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--(TRIM($I$1:$I$200)="stock"),--($K$1:$K$200="x")) Does that help? Regards, Paul -- "hockeyb9" wrote in message ... hey guys, i have tried formula both ways. i hvae rechecked the data to ensure matches - i actually copied the match cell from data. still getting the DIV/0! i really appreciate your help in trying to figure this out because i am stumped as to why it doesn't work. "PCLIVE" wrote: Actually yes. I don't know if something quirky is happening...but all four formulas, including yours and the OPs, gives the same result. Should it not work? -- "Mike H" wrote in message ... Hmmm, You got his formula to work as posted? For me it produces a value error as does your second. Mike "PCLIVE" wrote: What's not working? Though you have an unnecessary ( ), it seems to work with your formula or either of the following: =SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x")) =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) Your sample data only has one match and therefore is divided by 1. HTH, Paul -- "hockeyb9" wrote in message ... i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
Good point Mike! I didn't use the headers from the example.
-- "Mike H" wrote in message ... The OP's didn't for me. The reason is because I used the data layout as supplied by the OP and with a header that formula will fail status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 Mike "PCLIVE" wrote: Actually yes. I don't know if something quirky is happening...but all four formulas, including yours and the OPs, gives the same result. Should it not work? -- "Mike H" wrote in message ... Hmmm, You got his formula to work as posted? For me it produces a value error as does your second. Mike "PCLIVE" wrote: What's not working? Though you have an unnecessary ( ), it seems to work with your formula or either of the following: =SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x")) =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) Your sample data only has one match and therefore is divided by 1. HTH, Paul -- "hockeyb9" wrote in message ... i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average with 2 criteria
hockeyb9 wrote:
i am trying to set up an average days for inventory based on two criteria. i have set up a calculation to get the number of days aged already, but can't get a sumproduct formula to work. ie) columns i k L status financed by days aged stock x 10 customer x 45 stock y 14 customer z 80 customer x 12 i tried =(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" )))) to no avail. anyone have an idea / solution? thanks How about a pivot table? Select columns I:L (assuming there is some extraneous data in J), create the pivot table. Drag Status to the row area. Drag Financed By to the column area. Drag Days Aged to the data area. Double click the button this creates and change Summarize by: to Average No muss, no fuss. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average with Criteria | Excel Worksheet Functions | |||
| need to average based on criteria | Excel Discussion (Misc queries) | |||
| Finding Average with Criteria | Excel Worksheet Functions | |||
| Average given criteria, HELP! | Excel Worksheet Functions | |||
| Average function and two criteria | Excel Worksheet Functions | |||
Linear Mode
