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.

 

[Peter_SSs]

Welcome to MrExcel!

Does this do what you want?

=IF(COUNT(B1:B4)=4,MATCH(AVERAGE(B1:B4),{0,0.35,0.5,0.7},1),"")

 

[ryan01]

Thank you for your speedy reply. Unfortunately all it's doing now is tallying up the amount of cells in the range.

It keeps kicking out a 4.

I will see what I can do. Thank you!

 

[Peter_SSs]

Thank you for your speedy reply. Unfortunately all it's doing now is tallying up the amount of cells in the range.

It keeps kicking out a 4.

What do you mean by this?

Here are a few examples using my formula. (If rows 1-4 are complete, I have put the average in row 7 just so you can see what it is.)

The formula in B4 (and B7) copied across. Are these the results you would expect for these data samples?

If not, can you provide afew sets of sample data and the expected results?

Excel Workbook
	A	B	C	D	E
1		30%	10%	88%	38%
2		25%	11%	25%	52%
3		80%	15%		45%
4		90%	10%	16%	45%
5	Result -->	3	1		2
6
7	Average rows 1-4 -->	56%	12%		45%
Average to return value

 

[jasonb75]

I would take a guess that the cells are not formatted as %, in which case you would need to modify Peter's formula slightly

=IF(COUNT(B1:B4)=4,MATCH(AVERAGE(B1:B4),{0,35,50,70},1),"")

 

[ryan01]

Hi guys

Thank you so much for your help. Yes the cells are not formatted in %. I realised that when I looked at the formula again.

I works perfectly.

May I ask another question? Hope you don't mind.

The learners at the school all get a learner id. It consists of their date of birth, the initials and then either 10001 or 20001 depending on whether boy or girl. If there is more than one with the same initials then it becomes 10002, 10003 and same for the girls.

So it would look something like this. Daniel Jones 990504DJ10001. Boys start with 1000 and the girls with 2000(1,2,3, etc).

I get the class list and then I have to split the boys and girls alphabetically on 2 separate sheets.

What formula could I use to do that?

I'm sorry to bother you guys like this.

Thanks so much.

 

[rockdrigotoca]

Hi, if you just want to split by girls and boys (and then do the sorting by Tools --> Sort) you could use something like:

(assuming the ID is on B1)

<code>
=if(right(B1)<1999,"Girl","Boy")
</code>

Hope it helps!

 

[ryan01]

Thank you for the help.

I think I might not have misstated how I wanted it sorted. The boys all have 10001(2,3,etc) after their dob and initials as with Daniel. eg 990405DJ10001.
If it's a girl then that 10001 changes to 20001.

Does that make more sense? Should I give more examples? And once I have them sorted, they need to go to separate worksheets in the same workbook.

Thank you for you help!

 

[rockdrigotoca]

Hey Ryan,

I think what you could do is put this formula on any column, assuming your student ID is in Column B

=MID(B2,9,1)

Then this will return either 1 or 2

You can then Sort or Filter these results and copy into a different page.

 

[jasonb75]

Hey Ryan,

I think what you could do is put this formula on any column, assuming your student ID is in Column B

=MID(B2,9,1)

Then this will return either 1 or 2

You can then Sort or Filter these results and copy into a different page.

That won't work if there are 3 (or more) initials.


You can't 'send' the names to another sheet using formula, only VBA, as an alternative.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 85px"><COL style="WIDTH: 99px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Daniel Jones</TD><TD>990504DJ10001</TD><TD>Boy</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>=IF(RIGHT(B1,5)+0>20000,"Girl","Boy")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

Sort by column C then column A and copy / paste boys to one sheet, girls to another.