Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Excel Formula

  1. #1
    New Member
    Join Date
    Jun 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Location
    seated
    Posts
    3,840
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula

    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?
    Φ(𝑘)𝛼Α𝑡
    O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    Jun 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula

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

  4. #4
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Location
    seated
    Posts
    3,840
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula

    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.
    Φ(𝑘)𝛼Α𝑡
    O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    Jun 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  6. #6
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Location
    seated
    Posts
    3,840
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    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?
    Φ(𝑘)𝛼Α𝑡
    O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    Jun 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Location
    seated
    Posts
    3,840
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula

    Quote Originally Posted by billyc View Post
    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



    Φ(𝑘)𝛼Α𝑡
    O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    Jun 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Location
    seated
    Posts
    3,840
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?
    Φ(𝑘)𝛼Α𝑡
    O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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