Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to solve
I have two columns with information which I need to collate.
The first column has variables such as apples, grapes, pears The second column rates the first column from 1 to 5. I want to count how many "1" ratings I get for apples and how many "2" ratings and so on for each of the items in the first column. I think I should be using the €ścountif€ť function together with €śand€ť function but I just cant work it out. Any ideas would be very helpful. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to solve
In article ,
Ben wrote: I have two columns with information which I need to collate. The first column has variables such as apples, grapes, pears The second column rates the first column from 1 to 5. I want to count how many "1" ratings I get for apples and how many "2" ratings and so on for each of the items in the first column. I think I should be using the €ścountif€ť function together with €śand€ť function but I just cant work it out. Any ideas would be very helpful. Thanks I'd recommend using a Pivot Table: http://peltiertech.com/Excel/Pivots/pivotstart.htm But if you want to do it with formulae, try: =SUMPRODUCT(--(A1:A1000="apples"),--(B1:B100=1)) See http://www.mcgimpsey.com/excel/doubleneg.html for an explanation. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to solve
Do you want this as a little table, with Apples, Grapes, Pears etc
going down column A (in a separate sheet) and 1, 2, 3 etc going across row 1 ? If so, then ensure you have a header row in your first sheet, eg "Variable" in A1 and "Rating" in B1, and insert a new worksheet. Highlight all the data and the heading in column A of Sheet1 and copy it to column A of Sheet2. While the data is still highlighted, click on Data | Filter | Advanced filter and in the pop-up click on Unique Records Only, and Copy to another location - enter C1 in the Copy To box and click OK. You will now have a unique list of your variables in column C of Sheet2, and you can delete columns A and B so that the variables list is now in column A. Enter 1 to 5 in B1 to F1, and in B2 enter this formula: =SUMPRODUCT((Sheet1!$A$2:$A$500=$A2)*(Sheet1!$B$2: $B$500=B$1)) This assumes you have 500 rows in Sheet1 - adjust as necessary, but make sure both arrays are the same size. Copy the formula into C2:F2, and then copy B2:F2 down for as many items as you have in column A. You now have your ratings table against each variable. In a case like this you might have a number of zeros, and you can hide these if you like by highlighting from B2 down to F-whatever and clicking Format | Conditional Formatting and select Cell Contents in the first panel, Equal to in the second panel and 0 (zero) in the 3rd panel. Then click on the Format button and select Colour and set to white, then OK twice to get out of this. This will now show all zero values as white on white, effectively making the cells appear blank to allow the rating numbers to stand out. Hope this helps. Pete On Aug 7, 11:30 pm, Ben wrote: I have two columns with information which I need to collate. The first column has variables such as apples, grapes, pears The second column rates the first column from 1 to 5. I want to count how many "1" ratings I get for apples and how many "2" ratings and so on for each of the items in the first column. I think I should be using the "countif" function together with "and" function but I just can't work it out. Any ideas would be very helpful. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to solve | Excel Discussion (Misc queries) | |||
simple average formula...can you solve it in principle? | Excel Discussion (Misc queries) | |||
How do I make FORMULA TO SOLVE CURVE OR 2ND ORDER EQUATION? | Excel Worksheet Functions | |||
is there anything to solve this? | Excel Worksheet Functions | |||
How to solve a formula so that it returns actual answer if negati. | Excel Discussion (Misc queries) |