Excel Formula

[billyc]

Hi,

I am a novice to excel. I have provided some mock information below, column A the Grade, B the score, I have calculated the column C using this function =IF(B2:B100>A2:A100,"Exceeding",IF(B2:B100 However, my issue is that I need to display the data in column C on a separate sheet. I cannot have it displayed on this sheet next to column B as the data is extensive in reality and this column will confuse the users. So my question is what formula do I need so that I can generate the column C information in sheet 2. Hope this is clear.

Thanks


A B C
Grade Score
1 3 Exceeding
1 1 At
1 1 At
2 1 Below
2 2 At
1 0 Below

[cyrilbrd]

Given in sheet1:

Excel 2010

	A	B
1	Grade	Score
2	1	3
3	1	1
4	1	1
5	2	1
6	2	2
7	1	0

billycSheet1

Then in sheet2 we have:

Excel 2010

	A
1	Result
2	Exceeding
3	At
4	At
5	Below
6	At
7	Below

billycSheet2

Formula is =IF(billycSheet1!A2>billycSheet1!B2,"Below",IF(billycSheet1!A2=billycSheet1!B2,"At","Exceeding"))

Would that be close to what you needed?

[billyc]

Thank you so much, not sure if I completely understand the formula but it definitely works so thank you again!

[cyrilbrd]

You are most welcome.
Glad it works for you.
The formula is simple, it uses the format IF(logical_test, value_if_true,Value_if_false)
Here the logical test is A2 > B2
1- IF A2> B2 then it will return "below" (that is the first part of the IF)
2- Then IF (again) A2 = B2, then it will return "At".
3- The last statement is not needed, as the only thing remaining is A2 < B2, we need just type the value if true being "exceeding".

Hope this clarify things a bit.

[billyc]

Thank you, this makes a lot more sense. I do have one other question (which I can post in another thread if it is not appropriate to ask again!) However, Is there a formula which will allow me to average a column dependent on another.

For example

A B
Grade Score
1 3
1 3
1 2
2 2
2 3
2 4

So if column A = 1 then I need to add and average all of the scores for the grade 1's and then the same for the grade 2's. I have been trying =IF(A2:A7,"F",SUM(B3:B8)/COUNT(B3:B8)) but with no luck, I think I am on the right track, but my excel brain is not quite up to scratch as yet. I am also trying to account for cells which have no score entered.

Thanks in advance, as I said I can re-post if necessary.

Billy

[cyrilbrd]

It is alright to ask, you are the OP and this is still related to your thread. No worries.

Excel 2010

	A	B
1	Grade	Score
2	1	3
3	1	1
4	1	1
5	2	1
6	2	2
7	1	0
8
9	Grade	average
10	1	1.25
11	2	1.5

billycSheet1

The average in cell B10 could be computed with the formula =AVERAGEIF($A$2:$A$7,A10,$B$2:$B$7)

Would that be what you needed?

[billyc]

Hi,

This is fantastic, looks so simple when you do it, and it makes perfect sense. Just wondering if there is any way to allow for cells which are empty so the average will still be calculated correctly.

Thanks
Billy.

[cyrilbrd]

Hi,

This is fantastic, looks so simple when you do it, and it makes perfect sense. Just wondering if there is any way to allow for cells which are empty so the average will still be calculated correctly.

Thanks
Billy.

Give me an example of "empty cell" are those blanks or with space or text or zero?
which one is more accurate, ave1 or ave2?
Excel 2010

	A	B	C
1	Grade	Score
2	1	3
3	1	1
4	1
5	2	1
6	2	2
7	1	0
8	2	0
9
10	Grade	ave1	ave2
11	1	1.333333	2
12	2	1	1.5

billycSheet1

[billyc]

I need the formula to ignore the blank cells, but count the cells which have a 0 if possible. As a score of 0 indicates a score which will lower the average, a blank means the test has not been completed so the average should not be affected.

[cyrilbrd]

so in the example I provided, ave1 would return your query?
example 3,1,blank, 0 are equivalent to three (3) entries summing 4, average is 1.333 ((3+1+0)/3) yes?