Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If A1=this or that or even the other?????
I am trying to produce an accuracy report (we'll call spreadsheet 2) that
updates as i type in my information on main sheet (spreadsheet 1.) Spreadsheet 2 would need to recognise the "company name" (for example we'll use the phonetic alphabet) for column A and a "y" or "n" for another column B to indicate whether or not the delivery is correct or not. Spreadsheet 1 A B 1 company correct 2 alpha y 3 bravo y 4 charlie n 5 alpha n 6 alpha y 7 bravo n 8 charlie y 9 charlie y 10 charlie y Would there be a formula that enables spreadsheet 2 to show the % of correct alpha deliveries, and Bravo etc. Using the information in spreadsheet 1(ss1), i would like spreadsheet 2(ss2) to look like the example below. Spreadsheet 2 A B C 1 company total deliveries % correct 2 alpha 3 66.66 3 bravo 2 50 4 charlie 4 75 Is there such a function available in Excel that "IF A2 on ss1=alpha then add 1 to B2 on ss2, if A2 on ss1=bravo then add 1 to B3 on ss2, if A2 on ss1=charlie then add 1 to B4 on ss2" ETC. Then having it recognise wether or not the delivery was correct, then working out a correct %. Any help on this matter would be very much appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If A1=this or that or even the other?????
Assuming source data in Sheet1, cols A and B
In Sheet2, Put in B2: =COUNTIF(Sheet1!A:A,A2) Put in C2: =SUMPRODUCT((Sheet1!$A$2:$A$10=A2)*(Sheet1!$B$2:$B $10="y"))/B2 Format C2 as percentage? to taste. Adapt the ranges to suit. Then select B2:C2, fill down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "B.H.JIG" wrote: I am trying to produce an accuracy report (we'll call spreadsheet 2) that updates as i type in my information on main sheet (spreadsheet 1.) Spreadsheet 2 would need to recognise the "company name" (for example we'll use the phonetic alphabet) for column A and a "y" or "n" for another column B to indicate whether or not the delivery is correct or not. Spreadsheet 1 A B 1 company correct 2 alpha y 3 bravo y 4 charlie n 5 alpha n 6 alpha y 7 bravo n 8 charlie y 9 charlie y 10 charlie y Would there be a formula that enables spreadsheet 2 to show the % of correct alpha deliveries, and Bravo etc. Using the information in spreadsheet 1(ss1), i would like spreadsheet 2(ss2) to look like the example below. Spreadsheet 2 A B C 1 company total deliveries % correct 2 alpha 3 66.66 3 bravo 2 50 4 charlie 4 75 Is there such a function available in Excel that "IF A2 on ss1=alpha then add 1 to B2 on ss2, if A2 on ss1=bravo then add 1 to B3 on ss2, if A2 on ss1=charlie then add 1 to B4 on ss2" ETC. Then having it recognise wether or not the delivery was correct, then working out a correct %. Any help on this matter would be very much appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If A1=this or that or even the other?????
Hi
Try something like this: =SUMPRODUCT((A1:A10="charlie")*(B1:B10="y"))/COUNTIF(A1:A10,"charlie") format the cell as % and use the countif fomrula to get a total number of deliveries. HTH Jean-Guy "B.H.JIG" wrote: I am trying to produce an accuracy report (we'll call spreadsheet 2) that updates as i type in my information on main sheet (spreadsheet 1.) Spreadsheet 2 would need to recognise the "company name" (for example we'll use the phonetic alphabet) for column A and a "y" or "n" for another column B to indicate whether or not the delivery is correct or not. Spreadsheet 1 A B 1 company correct 2 alpha y 3 bravo y 4 charlie n 5 alpha n 6 alpha y 7 bravo n 8 charlie y 9 charlie y 10 charlie y Would there be a formula that enables spreadsheet 2 to show the % of correct alpha deliveries, and Bravo etc. Using the information in spreadsheet 1(ss1), i would like spreadsheet 2(ss2) to look like the example below. Spreadsheet 2 A B C 1 company total deliveries % correct 2 alpha 3 66.66 3 bravo 2 50 4 charlie 4 75 Is there such a function available in Excel that "IF A2 on ss1=alpha then add 1 to B2 on ss2, if A2 on ss1=bravo then add 1 to B3 on ss2, if A2 on ss1=charlie then add 1 to B4 on ss2" ETC. Then having it recognise wether or not the delivery was correct, then working out a correct %. Any help on this matter would be very much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|