Excel Formula

billyc

New Member
Joined
Jun 9, 2014
Messages
12
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<A2:A100,"Below",IF(B2:B100=A2:A100,"At")))
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Given in sheet1:


Excel 2010
AB
1GradeScore
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?
 
Upvote 0
Thank you so much, not sure if I completely understand the formula but it definitely works so thank you again!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
It is alright to ask, you are the OP and this is still related to your thread. No worries.


Excel 2010
AB
1GradeScore
213
311
411
521
622
710
8
9Gradeaverage
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?
 
Upvote 0
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.
 
Upvote 0
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
1GradeScore
213
311
41
521
622
710
820
9
10Gradeave1ave2
1111.3333332
12211.5
billycSheet1
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top