Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula needed
I need to be able to work out the percentage of certain orders placed by
certain groups. In column A I have numbers which determines what kind of order it is, ie 3000??????? determines a catalogue order any other kind of number is either a special request or service order. Column G determines what group made the purchase. I have a list of all the groups which I would like the % for stored in a named range 'Pgrps'. I was thinkin of using sumproduct but I cant get the syntax right. Basically the formula should look like Sumproduct(range("A1:A5000"="3000")*Range("G1:G500 0"=Pgrps)/Count("A1:A5000") Can anyone help me with this? TIA |
#2
|
|||
|
|||
=SUMPRODUCT((A1:A5000=3000)*(ISNUMBER(MATCH
(G1:G5000,Pgrps,0))))/COUNT(A1:A5000) If col. A is text, use "3000" and COUNTA in the formula HTH Jason Atlanta, GA -----Original Message----- I need to be able to work out the percentage of certain orders placed by certain groups. In column A I have numbers which determines what kind of order it is, ie 3000??????? determines a catalogue order any other kind of number is either a special request or service order. Column G determines what group made the purchase. I have a list of all the groups which I would like the % for stored in a named range 'Pgrps'. I was thinkin of using sumproduct but I cant get the syntax right. Basically the formula should look like Sumproduct(range("A1:A5000"="3000")*Range ("G1:G5000"=Pgrps)/Count("A1:A5000") Can anyone help me with this? TIA . |
#3
|
|||
|
|||
Hi
I have tried your example but I am still having problems. I should have mentioned that as the report comes from our accounting system all values are stored as text. I tried amending your formula to reflect this and also the fact that the text in column A is 10 characters but I only need to test the first 4. =SUMPRODUCT((LEFT(A2:A18172,4)="3000")*(MATCH(G1:G 18172,PGrps,0)))/COUNTA(A2:A18172) Can anyone help correct this? TIA "Jason Morin" wrote: =SUMPRODUCT((A1:A5000=3000)*(ISNUMBER(MATCH (G1:G5000,Pgrps,0))))/COUNT(A1:A5000) If col. A is text, use "3000" and COUNTA in the formula HTH Jason Atlanta, GA -----Original Message----- I need to be able to work out the percentage of certain orders placed by certain groups. In column A I have numbers which determines what kind of order it is, ie 3000??????? determines a catalogue order any other kind of number is either a special request or service order. Column G determines what group made the purchase. I have a list of all the groups which I would like the % for stored in a named range 'Pgrps'. I was thinkin of using sumproduct but I cant get the syntax right. Basically the formula should look like Sumproduct(range("A1:A5000"="3000")*Range ("G1:G5000"=Pgrps)/Count("A1:A5000") Can anyone help me with this? TIA . |
#4
|
|||
|
|||
Edgar-
Your formula looks fine except you left out the ISNUMBER portion of it. Jason -----Original Message----- Hi I have tried your example but I am still having problems. I should have mentioned that as the report comes from our accounting system all values are stored as text. I tried amending your formula to reflect this and also the fact that the text in column A is 10 characters but I only need to test the first 4. =SUMPRODUCT((LEFT(A2:A18172,4)="3000")*(MATCH (G1:G18172,PGrps,0)))/COUNTA(A2:A18172) Can anyone help correct this? TIA "Jason Morin" wrote: =SUMPRODUCT((A1:A5000=3000)*(ISNUMBER(MATCH (G1:G5000,Pgrps,0))))/COUNT(A1:A5000) If col. A is text, use "3000" and COUNTA in the formula HTH Jason Atlanta, GA -----Original Message----- I need to be able to work out the percentage of certain orders placed by certain groups. In column A I have numbers which determines what kind of order it is, ie 3000??????? determines a catalogue order any other kind of number is either a special request or service order. Column G determines what group made the purchase. I have a list of all the groups which I would like the % for stored in a named range 'Pgrps'. I was thinkin of using sumproduct but I cant get the syntax right. Basically the formula should look like Sumproduct(range("A1:A5000"="3000")*Range ("G1:G5000"=Pgrps)/Count("A1:A5000") Can anyone help me with this? TIA . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula needed | Excel Worksheet Functions | |||
Help, Excel Formula Needed -- Urgent | Excel Worksheet Functions | |||
Look Up and Cell Reference - Formula Help Needed | Excel Worksheet Functions | |||
Formula needed | Excel Worksheet Functions | |||
Complex formula help needed | Excel Worksheet Functions |