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

 

[billyc]

Yes...!

 

[cyrilbrd]

use =AVERAGEIF($A$2:$A$8,A11,$B$2:$B$8), this will ignore blank cells and acknowledge zeros.

 

[billyc]

Yes...ave1 would be correct.

 

[billyc]

Thank you...sorry trying to reply with three kids trying to press keys...I really appreciate your help. Have a nice evening.
Billy.

 

[cyrilbrd]

Thank you...sorry trying to reply with three kids trying to press keys...I really appreciate your help. Have a nice evening.
Billy.

No worries, glad we had it sorted out, enjoy your evening with the kids!
Cheers.

 

[billyc]

Hi,

Just put the formula in and for some reason it is not ignoring the blanks...sorry...should have double checked at the time.

Thanks
Billy

 

[billyc]

More info. This is the formula I have used for my set of data. The answer is probably obvious!!! =AVERAGEIF($B$6:$B$31,A33,$E$6:$E$31)

 

[billyc]

Just tried it in excel, it works, but just not working in google docs (excel), must be an issue with the transfer to google docs. Thanks

 

[cyrilbrd]

Noted. Thanks for the feedback.

 

[billyc]

Hi,

Just completing the thread in case anyone looks it up. So here is the formula used in google docs, could not leave cells blank so this is solving the issue for now.

Thanks
Billy

=IFERROR(AVERAGEIF($B$6:$B$31,<wbr>$A33,E$6:E$31),"no data")
​