|
How do I get an average sale if multiple columns?
Column I J N
11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
How do I get an average sale if multiple columns?
Hi!
Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4)) Better to use cells to hold the criteria: A1 = 11 B1 = DP =AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4)) Biff "Todd" wrote in message ... Column I J N 11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
How do I get an average sale if multiple columns?
Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the response
"Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4)) Better to use cells to hold the criteria: A1 = 11 B1 = DP =AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4)) Biff "Todd" wrote in message ... Column I J N 11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
How do I get an average sale if multiple columns?
Do you have #N/A's in any of the ranges?
Biff "Todd" wrote in message ... Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the response "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4)) Better to use cells to hold the criteria: A1 = 11 B1 = DP =AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4)) Biff "Todd" wrote in message ... Column I J N 11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
How do I get an average sale if multiple columns?
This is a multi worksheet workbook. It is a lead and sales tracking
workbook. It does not contain any #N/A's. I have to show the average sale by salesperson by month & for the year. Here is a better way of putting it. Column I J N 11 DP $1,300 12 JJ $2,000 21 DP $12,000 21 AF $20,000 41 AF $8,000 11 DP $1,500 If Column I = 11 and J = DP, what is the average sale (column n). For my month of july I start @ row 217 and end @ 300. The formula the way I entered it is =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N1:N4)). The 'S'!(S!) represents a worksheet (The S stands for Sales). Is this more helpful? All thanks Todd "Biff" wrote: Do you have #N/A's in any of the ranges? Biff "Todd" wrote in message ... Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the response "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4)) Better to use cells to hold the criteria: A1 = 11 B1 = DP =AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4)) Biff "Todd" wrote in message ... Column I J N 11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
How do I get an average sale if multiple columns?
=AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3) ,S!N1:N4))
The size of the ranges MUST be exactly the same: N1:N4 is not the same size as I217:I300 and J217:J300 Maybe you meant: =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N217:N300)) Biff "Todd" wrote in message ... This is a multi worksheet workbook. It is a lead and sales tracking workbook. It does not contain any #N/A's. I have to show the average sale by salesperson by month & for the year. Here is a better way of putting it. Column I J N 11 DP $1,300 12 JJ $2,000 21 DP $12,000 21 AF $20,000 41 AF $8,000 11 DP $1,500 If Column I = 11 and J = DP, what is the average sale (column n). For my month of july I start @ row 217 and end @ 300. The formula the way I entered it is =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N1:N4)). The 'S'!(S!) represents a worksheet (The S stands for Sales). Is this more helpful? All thanks Todd "Biff" wrote: Do you have #N/A's in any of the ranges? Biff "Todd" wrote in message ... Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the response "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4)) Better to use cells to hold the criteria: A1 = 11 B1 = DP =AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4)) Biff "Todd" wrote in message ... Column I J N 11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
How do I get an average sale if multiple columns?
Biff,
For that formula i am getting 1247.45. The totals that equal that equation are, (6866,6597,5084,6025,6589,27826,2632=61619 /7=8,802.71) Obviously, there is something not working. I am hitting the Ctrl-Shift-Enter for the array and all of my formulas now are exactly the same (thanks for noticing that though). I am definetly scratching my head on this one. Thanks for your help so far. Todd "Biff" wrote: =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3) ,S!N1:N4)) The size of the ranges MUST be exactly the same: N1:N4 is not the same size as I217:I300 and J217:J300 Maybe you meant: =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N217:N300)) Biff "Todd" wrote in message ... This is a multi worksheet workbook. It is a lead and sales tracking workbook. It does not contain any #N/A's. I have to show the average sale by salesperson by month & for the year. Here is a better way of putting it. Column I J N 11 DP $1,300 12 JJ $2,000 21 DP $12,000 21 AF $20,000 41 AF $8,000 11 DP $1,500 If Column I = 11 and J = DP, what is the average sale (column n). For my month of july I start @ row 217 and end @ 300. The formula the way I entered it is =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N1:N4)). The 'S'!(S!) represents a worksheet (The S stands for Sales). Is this more helpful? All thanks Todd "Biff" wrote: Do you have #N/A's in any of the ranges? Biff "Todd" wrote in message ... Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the response "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4)) Better to use cells to hold the criteria: A1 = 11 B1 = DP =AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4)) Biff "Todd" wrote in message ... Column I J N 11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
How do I get an average sale if multiple columns?
Can you send me a copy of the file?
If so, just let me know how to contact you. Biff "Todd" wrote in message ... Biff, For that formula i am getting 1247.45. The totals that equal that equation are, (6866,6597,5084,6025,6589,27826,2632=61619 /7=8,802.71) Obviously, there is something not working. I am hitting the Ctrl-Shift-Enter for the array and all of my formulas now are exactly the same (thanks for noticing that though). I am definetly scratching my head on this one. Thanks for your help so far. Todd "Biff" wrote: =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3) ,S!N1:N4)) The size of the ranges MUST be exactly the same: N1:N4 is not the same size as I217:I300 and J217:J300 Maybe you meant: =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N217:N300)) Biff "Todd" wrote in message ... This is a multi worksheet workbook. It is a lead and sales tracking workbook. It does not contain any #N/A's. I have to show the average sale by salesperson by month & for the year. Here is a better way of putting it. Column I J N 11 DP $1,300 12 JJ $2,000 21 DP $12,000 21 AF $20,000 41 AF $8,000 11 DP $1,500 If Column I = 11 and J = DP, what is the average sale (column n). For my month of july I start @ row 217 and end @ 300. The formula the way I entered it is =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N1:N4)). The 'S'!(S!) represents a worksheet (The S stands for Sales). Is this more helpful? All thanks Todd "Biff" wrote: Do you have #N/A's in any of the ranges? Biff "Todd" wrote in message ... Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the response "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4)) Better to use cells to hold the criteria: A1 = 11 B1 = DP =AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4)) Biff "Todd" wrote in message ... Column I J N 11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
How do I get an average sale if multiple columns?
Ok, sent an email.
Biff "Todd" wrote in message ... is my e-mail Thanks "Biff" wrote: Can you send me a copy of the file? If so, just let me know how to contact you. Biff "Todd" wrote in message ... Biff, For that formula i am getting 1247.45. The totals that equal that equation are, (6866,6597,5084,6025,6589,27826,2632=61619 /7=8,802.71) Obviously, there is something not working. I am hitting the Ctrl-Shift-Enter for the array and all of my formulas now are exactly the same (thanks for noticing that though). I am definetly scratching my head on this one. Thanks for your help so far. Todd "Biff" wrote: =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3) ,S!N1:N4)) The size of the ranges MUST be exactly the same: N1:N4 is not the same size as I217:I300 and J217:J300 Maybe you meant: =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N217:N300)) Biff "Todd" wrote in message ... This is a multi worksheet workbook. It is a lead and sales tracking workbook. It does not contain any #N/A's. I have to show the average sale by salesperson by month & for the year. Here is a better way of putting it. Column I J N 11 DP $1,300 12 JJ $2,000 21 DP $12,000 21 AF $20,000 41 AF $8,000 11 DP $1,500 If Column I = 11 and J = DP, what is the average sale (column n). For my month of july I start @ row 217 and end @ 300. The formula the way I entered it is =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3), S!N1:N4)). The 'S'!(S!) represents a worksheet (The S stands for Sales). Is this more helpful? All thanks Todd "Biff" wrote: Do you have #N/A's in any of the ranges? Biff "Todd" wrote in message ... Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the response "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: How to enter an array formula: http://cpearson.com/excel/array.htm =AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4)) Better to use cells to hold the criteria: A1 = 11 B1 = DP =AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4)) Biff "Todd" wrote in message ... Column I J N 11 DP 3100 21 AF 10000 41 AF 8000 21 DP 12000 How do I get the average sale if column N=sale amount if I=11 and J=DP or if I=21 J=AF Average Sale=? Please help me. Thanks Todd |
| All times are GMT +1. The time now is 01:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com