Help!!!!!!!!!!!!!!!!!!!

[ryan01]

Hi

Totally new here. Need some help if can be.

I have to write a formula that averages cells in a range once the range is full. That I can do.

My problem is-how do I then write it so that it uses that data and converts it into a value depending on the average.

Here is what I am trying to do:

Cells B1,2,3 and 4 get a percentage(a pupil's mark for eg English). This is tthe end of the term percentage. I want B5 to work out the average only once all four terms marks have been entered.

This average must then be converted into a code. If the % is between 0-34 it is given a code of 1, if between 35 and 50 its a 2, 50-70 is a 3 and 70-100 is a 4.

My question is: How do I get the cell, B5 to display the code eg 1,2,3,4 instead of the average %?

I can write the if to calculate the code and the average seperately. But I can put them together.

Any help would b greatly appreciated.

 

[ryan01]

Thanks very much both of you. As i don't know how to write code, I'll stick to filtering and copy/paste before I start messing up data....

 

[Peter_SSs]

If I have understood correctly, it seems that you want to use the IDs to split and sort the list. If that is the case ..

- you would be just using initials to sort, and therefore be soting primarily on the first name initial.

- you may not get a perfect sort even on first name since only the first initial of the first name is known when looking at the code. (See my example that Ann Smith is sorted before Amanda Smith. This is because Ann Smith was listed first on the original list and therefore got the final digit "1" whereas Amanda has a final digit of "2")

In any case this makes an attempt to do a reasonable sort based on sex and initials (up to 4 initials). It also assumes that there will not be more than 9 people of the same sex with the same initials.

My other assumptions are

- that the original list does not go beyond row 1,000. If it does then the formulas would need amendment to reflect that.

- the codes are generated with upper case initials as per your one example.

I've used helper columns on the original sheet and the two 'gender' sheets. These columns could be hidden after they are populated.

So, the original list sheet (mine is called 'Combined'):
Formula in C2 is copied down.

Excel Workbook
	A	B	C
1	Name	ID
2	Daniel Jones	990504DJ10001	1687449481
3	Mark John Smith	981210MJS10001	1777483491
4	Ann Smith	990508AS20001	2658350481
5	Amanda Smith	991102AS20002	2658350482
6	John Jones	980712JJ10001	1747449481
7
Combined

Boys sheet:
C1 is a stand-alone formula.
C2 copied down as far as you might need.
A2 is copied across and then down as far as the column C formula.

Excel Workbook
	A	B	C
1	Name	ID	3
2	Daniel Jones	990504DJ10001	2
3	John Jones	980712JJ10001	6
4	Mark John Smith	981210MJS10001	3
5
Boys

Girls sheet:
C1 is a stand-alone formula.
C2 copied down as far as you might need.
A2 is copied across and then down as far as the column C formula.

Excel Workbook
	A	B	C
1	Name	ID	2
2	Ann Smith	990508AS20001	4
3	Amanda Smith	991102AS20002	5
4
Girls

 

[ryan01]

Peter

You are awesome! Thanks a lot!

 

[Peter_SSs]

Cheers, glad it worked for you.

If you want to shorten the column C formulas on the Girls sheet, the formula in C2 (copied down) could be:

=IF(ROWS(E$2:E2)>C$1,"",MATCH(SMALL(Combined!C$1:C$1000,ROWS(E$2:E2)+Boys!C$1),Combined!C$1:C$1000,0))

 

[jasonb75]

Peter,

What do you think of using

=--(LEFT(RIGHT(B2,5))&TEXT(1+COUNTIF($A:$A,"<"&A2),"0000"))

in the helper column of the combined sheet and the 2 countifs adjusted to reflect that change?

This overcomes the 'perfect sort' that you mentioned, but can you see any other problems that I've overlooked?

 

[Peter_SSs]

Jason

If the OP wants to sort on full name (with first names as primary sort) then that would probably serve the purpose fine, though you wouldn't really need the "1+" in the middle of that formula. I would probably also try to avoid the whole column reference so maybe something like this:

=LEFT(RIGHT(B2,5))*10^4+COUNTIF(A$2:A$1000,"<"&A2)