Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel: what to put in formula bar

Options
  • 19-03-2014 8:41pm
    #1
    Andy From Sligo
    Registered Users Posts: 11,794 ✭✭✭✭


    Hi, wonder if someone can help me. I am trying to make a simple chart in Excel but when it comes to putting in the formula in B10 to give me the difference of units over the week its all coming out wrong, am sure its a simple formula that im missing. Have tried B10 =SUM(B2,B3,B4,B5,B6,B7,B8) but that just gives me a total - then I tried =SUM(B8-B7-B6-B5-B4-B3-B2) and thats giving me the wrong figure in B10 .. what the hell am i doing wrong? B10 should = just 2 - heres a pic:

    spreadsh.jpg
    Tagged:


Comments

  • Options
    #2
    Qwerty?
    Registered Users Posts: 1,243 ✭✭✭Qwerty?


    Andy From Sligo wrote: »
    Hi, wonder if someone can help me. I am trying to make a simple chart in Excel but when it comes to putting in the formula in B10 to give me the difference of units over the week its all coming out wrong, am sure its a simple formula that im missing. Have tried B10 =SUM() but that just gives me a total - then I tried =SUM(B8-B7-B6-B5-B4-B3-B2) and thats giving me the wrong figure in B10 .. what the hell am i doing wrong? B10 should = just 2 - heres a pic:

    spreadsh.jpg

    Try:-

    =CountA(B2,B3,B4,B5,B6,B7,B8)

    actually

    =CountA(B2:B8) would be handier


  • Options
    #3
    Andy From Sligo
    Registered Users Posts: 11,794 ✭✭✭✭Andy From Sligo


    Qwerty? wrote: »
    Try:-

    =CountA(B2,B3,B4,B5,B6,B7,B8)

    actually

    =CountA(B2:B8) would be handier


    Thanks for the reply Qwerty - tried that but the total im getting is wrong though it should be 4 but im getting 3 ?!?

    SPREADS2.jpg


  • Options
    #4
    Qwerty?
    Registered Users Posts: 1,243 ✭✭✭Qwerty?


    Thats weird. Try just

    =Count(B2:B8)

    Count is the number of Cells containing Numbers
    CountA is the number of Cells not empty


  • Options
    #5
    Ted_YNWA
    Registered Users Posts: 71,799 ✭✭✭✭Ted_YNWA


    This should work for you.

    Gets the difference between highest & lowest, which by my reading ( no pun intended) of your post is what you are looking for.

    JpzkQkz.jpg


  • Options
    #6
    Andy From Sligo
    Registered Users Posts: 11,794 ✭✭✭✭Andy From Sligo


    Qwerty? wrote: »
    Thats weird. Try just

    =Count(B2:B8)

    Count is the number of Cells containing Numbers
    CountA is the number of Cells not empty


    no, very strange I tried that too and it gave the same number - I think count is just counting the items from B2 to B8 rather than counting the difference, if that makes sense.


  • Advertisement
  • Options
    #7
    Andy From Sligo
    Registered Users Posts: 11,794 ✭✭✭✭Andy From Sligo


    Ted_YNWA wrote: »
    This should work for you.

    Gets the difference between highest & lowest, which by my reading ( no pun intended) of your post is what you are looking for.

    JpzkQkz.jpg


    Yay! - brilliant that seems to have done what I want, thanks for that TED. however the scenario (and it wont ever arise so I dont care) is that if on Saturday the reading was lower say 79675 the total in box 10 would still be incorrect - but as I say i dont mind about that because the meter figure will always be rising (Unfortunately!!) - but thanks I now have the right formula. :)


Advertisement