Thanks:  0
Likes:  0

1. ## Excel Formula

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
1 3 Exceeding
1 1 At
1 1 At
2 1 Below
2 2 At
1 0 Below

2. ## Re: Excel Formula

Given in sheet1:

Excel 2010
AB
213
311
411
521
622
710

billycSheet1

Then in sheet2 we have:

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

billycSheet2

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

Would that be close to what you needed?

3. ## Re: Excel Formula

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

4. ## Re: Excel Formula

You are most welcome.
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.

5. ## Re: Excel Formula

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
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

6. ## Re: Excel Formula

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

Excel 2010
AB
213
311
411
521
622
710
8
1011.25
1121.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?

7. ## Re: Excel Formula

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.

8. ## Re: Excel Formula

Originally Posted by 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.
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
ABC
213
311
41
521
622
710
820
9
1111.3333332
12211.5

billycSheet1

9. ## Re: Excel Formula

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.

10. ## Re: Excel Formula

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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•