Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help please
First thank you for looking at this and sorry for the long post and formula,
if it's not clear please let me know. I'm trying to figure out how to re-write the below array formula: =COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes List V22 26Apr2007.xls]All'!$F$2:$F$5003<"",IF('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003<"",MATCH('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2)+1)) What this formula does is compares the id number in column A on both worksheets and then looks at column F to make sure it's not blank then counts all the unique supplier names in column P that aren't blank. The problem I have just run into is say the supplier has 3 parts and only prices 2 of them, the formula counts the supplier as complete when they aren't. Can this formula be written a different way to still only count the unique supplier names but have the criteria be to count them only if all parts are quoted? Thank you in advance for your help and sorry for the long post. Joe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help please
I don't quite understand. If F or P are blank, it doesn't count as an
instance, so what are blank in your examples that get counted? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe Gieder" wrote in message ... First thank you for looking at this and sorry for the long post and formula, if it's not clear please let me know. I'm trying to figure out how to re-write the below array formula: =COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes List V22 26Apr2007.xls]All'!$F$2:$F$5003<"",IF('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003<"",MATCH('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2)+1)) What this formula does is compares the id number in column A on both worksheets and then looks at column F to make sure it's not blank then counts all the unique supplier names in column P that aren't blank. The problem I have just run into is say the supplier has 3 parts and only prices 2 of them, the formula counts the supplier as complete when they aren't. Can this formula be written a different way to still only count the unique supplier names but have the criteria be to count them only if all parts are quoted? Thank you in advance for your help and sorry for the long post. Joe |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help please
Thank you for looking at this formula. I hope I have helped answer your
question. Say a supplier has 5 parts that they need to provide a price for and they only provide a quote on 1 of them, the way the formula works now is that once there is a price any one of those parts the formula counts that supplier as having fulfilled their obligation when they realy haven't. I'm trying to see if the formula can be written to ignore that supplier unless all the parts have been quoted. Supplier Price abc 1 abc 5 abc def 10 def 25 would like the formula to say 1 supplier left open Total suppliers = 2 (differnt formula) Suppliers fulfilling their quotes = 1 Supliers left to quote = 1 Currently the formula equals Suppliers fulfilling their quotes = 2 "Bob Phillips" wrote: I don't quite understand. If F or P are blank, it doesn't count as an instance, so what are blank in your examples that get counted? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe Gieder" wrote in message ... First thank you for looking at this and sorry for the long post and formula, if it's not clear please let me know. I'm trying to figure out how to re-write the below array formula: =COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes List V22 26Apr2007.xls]All'!$F$2:$F$5003<"",IF('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003<"",MATCH('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2)+1)) What this formula does is compares the id number in column A on both worksheets and then looks at column F to make sure it's not blank then counts all the unique supplier names in column P that aren't blank. The problem I have just run into is say the supplier has 3 parts and only prices 2 of them, the formula counts the supplier as complete when they aren't. Can this formula be written a different way to still only count the unique supplier names but have the criteria be to count them only if all parts are quoted? Thank you in advance for your help and sorry for the long post. Joe |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help please
But that is where I disagree with you, or don't understand, because that
formula counts the number of different values in column P for a given value in A. Your latest example suddenly ignores P. But as I said, If F or P are blank, it doesn't count as an instance to be counted anyway. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe Gieder" wrote in message ... Thank you for looking at this formula. I hope I have helped answer your question. Say a supplier has 5 parts that they need to provide a price for and they only provide a quote on 1 of them, the way the formula works now is that once there is a price any one of those parts the formula counts that supplier as having fulfilled their obligation when they realy haven't. I'm trying to see if the formula can be written to ignore that supplier unless all the parts have been quoted. Supplier Price abc 1 abc 5 abc def 10 def 25 would like the formula to say 1 supplier left open Total suppliers = 2 (differnt formula) Suppliers fulfilling their quotes = 1 Supliers left to quote = 1 Currently the formula equals Suppliers fulfilling their quotes = 2 "Bob Phillips" wrote: I don't quite understand. If F or P are blank, it doesn't count as an instance, so what are blank in your examples that get counted? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe Gieder" wrote in message ... First thank you for looking at this and sorry for the long post and formula, if it's not clear please let me know. I'm trying to figure out how to re-write the below array formula: =COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes List V22 26Apr2007.xls]All'!$F$2:$F$5003<"",IF('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003<"",MATCH('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2)+1)) What this formula does is compares the id number in column A on both worksheets and then looks at column F to make sure it's not blank then counts all the unique supplier names in column P that aren't blank. The problem I have just run into is say the supplier has 3 parts and only prices 2 of them, the formula counts the supplier as complete when they aren't. Can this formula be written a different way to still only count the unique supplier names but have the criteria be to count them only if all parts are quoted? Thank you in advance for your help and sorry for the long post. Joe |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help please
You are absolutly correct, the formula does not count the blanks. I just have
a hard time explaining what's going on but know what's happening. I think part of the problem lies within the MATCH part of the formula since it looks for the first match where both coulmn F and P are not blank and counts it. What I'm tying to do is look in column A for the matching ID number then look in column P for the suppliers matching the ID in column A (A is used like a filter) then look in column F to see if there are any blanks corresponding to the supplier in column P. The supplier name can appear multiple times (no matter how many times the same name appears it's only counted once) sometimes a price will be in column F and sometimes there wont be. If all the same supplier names have a value in column F have a price I want to count it if any one of them are blank I don't. What is happening is the supplier is getting counted even when there's no value in one of the corresponding column F cells. A F P 001 1 abc 001 2 abc 001 5 abc 001 2 def 001 def 001 1 ghi 002 10 abc 003 20 abc the ID to use 001 and the result should show 2 because supplier def has a blank in coulmn F even though there's a value with the other def. I hope this helps. Thanks Joe "Bob Phillips" wrote: But that is where I disagree with you, or don't understand, because that formula counts the number of different values in column P for a given value in A. Your latest example suddenly ignores P. But as I said, If F or P are blank, it doesn't count as an instance to be counted anyway. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe Gieder" wrote in message ... Thank you for looking at this formula. I hope I have helped answer your question. Say a supplier has 5 parts that they need to provide a price for and they only provide a quote on 1 of them, the way the formula works now is that once there is a price any one of those parts the formula counts that supplier as having fulfilled their obligation when they realy haven't. I'm trying to see if the formula can be written to ignore that supplier unless all the parts have been quoted. Supplier Price abc 1 abc 5 abc def 10 def 25 would like the formula to say 1 supplier left open Total suppliers = 2 (differnt formula) Suppliers fulfilling their quotes = 1 Supliers left to quote = 1 Currently the formula equals Suppliers fulfilling their quotes = 2 "Bob Phillips" wrote: I don't quite understand. If F or P are blank, it doesn't count as an instance, so what are blank in your examples that get counted? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe Gieder" wrote in message ... First thank you for looking at this and sorry for the long post and formula, if it's not clear please let me know. I'm trying to figure out how to re-write the below array formula: =COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes List V22 26Apr2007.xls]All'!$F$2:$F$5003<"",IF('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003<"",MATCH('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22 26Apr2007.xls]All'!$P$2)+1)) What this formula does is compares the id number in column A on both worksheets and then looks at column F to make sure it's not blank then counts all the unique supplier names in column P that aren't blank. The problem I have just run into is say the supplier has 3 parts and only prices 2 of them, the formula counts the supplier as complete when they aren't. Can this formula be written a different way to still only count the unique supplier names but have the criteria be to count them only if all parts are quoted? Thank you in advance for your help and sorry for the long post. Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|