Example: Student A is listed 4 times we want them in yellow. Student B is listed 5 times we would want them to be in blue. Student C is listed 2 times we would want them in yellow. We could just use two colors but we want the colors to change as the students change to that it is clear that she student changed.
First we need a way of finding how many times each name appears. This job is probably better done on Student ID numbers if they're available (to eliminate inconsistencies with spelling & capitalisation, etc.), but if names are all you have then we'll need to use them.
I assume you have a column full of names, some of which are repeated. Say this column runs from A2:A1000.
In a spare column (Say Col B), in B2:B1000 you can use the formula "=Countif(A$2:A$1000,A2)". This will then count up how many times a name is repeated and return that value next to each name.
Now to address the cell colour question.
This job is exactly what the Conditional Formatting feature is for.
Select the cell range that you want to format (as in the example above, the range containing the Student Names A2:A1000), and then go Format/Conditional Formatting. A dialog box will open, giving you an opportunity to add a formatting rule - assuming that you're using a version of Excel similar to mine, you'll need to click the + sign to add a rule.
There are some Automatic rules provided that make the process simpler (e.g. the 3-colour scale auto rule might suit your purpose), but I usually find that I use the manual method.
If the auto rule suits your purpose go ahead and use that, but if not, then choose 'Classic Style" and in the next drop-down box select "Use a formula to determine which cells to format".
Because we're using the most flexible rule writing style, this next part is a bit fiddly...what we need to do is write a series of formulas (formulae?) to describe the different formats we want to apply under each different circumstance.
With our 'Duplicate Counts' in cells B2:B1000, the first might be something like "=B2<=2" and then pick how you want that formatted (say background pink). The add another rule "=B2=3", and format that yellow. And then keep adding as many more rules as you need. Then just hit the OK buttons to finish the job.
Any 'Duplicate Count' values that are outside your set of rules will remain in their default format, and all those that match one of your rules will change to your chosen format for that rule. Each time a 'Duplicate Count' value changes, the format of the affected Student Name will also change.
That should do it.
Conditional formating should be able to this. But how is your data organized? (Column headers, Row headers etc.)
SOURCE: excel formula
Relative cell reference is address of cell , when copied from one cell to another gets changed automatically. e.g. Put formula in cell c5 as A5+1 when you copy this from c5 to c6 the address of A5 will automatically changed to A6. Absolute Cell Reference As above now put formula in cell C5 as $A$5+1 ,now copy this formula from C5 to C6 if you this formula youwill find cell Address of A5 does not changed. Mixed Cell Reference: If we put Dollar ($) sign before Alphabetic cell address i.e.$c5 then even if you copy this formula in any cell , coloumn (c) will remains constant. Similarly if we put Dollar ($) sign before Numeric cell address i.e.c$5 then you copy this formula in any cell row address of the cell remains same.(5). Pl elobarate on remaining two points 'Specific order of formulas ' and 'advance formulas,
SOURCE: % problems
You need to divide by three and multiply by .01 which will give you the average percetage or you need to divide the total by each entry and get invidual percentages
SOURCE: microsoft office home and student 2007
Sorry we are not permitted to support piracy.
Use Open office instead it looks like MS and is entirely free It readnd writes MS files and even looks like the MS product.
go to openoffice.org and download the complete set NO CHARGE and not crippled or timed
Formulas are used to specify calculations based on values in designated cells. Excel supports basic calculations as well as statistical, trigonometric and other specialized functions.
Formulas used in Excel must follow a certain syntax.
120 views
Usually answered in minutes!
×