Formula for NUMBERS??

Discussion in 'iWork Forum' started by vistas24, Dec 29, 2013.

  1. vistas24

    vistas24 iPF Noob

    Joined:
    Dec 29, 2013
    Messages:
    3
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    las vegas, nv
    Ratings:
    +0 / 0
    Hello... I am new to this forum sit. Not sure where to post my question. Or where to get any responses.. Total newbie. I am trying to figure out a formula on Numbers to get the average value of a column. I have a value for everyday of the month 1-31. The problem I am running into is on some days the value is zero and I don't want any days with a zero value equated into my monthly average..does that make sense? I appreciate any help. I may have posted this twice.. sorry.. I couldn't find it on the board the first time..
     
  2. giradman

    giradman iPad Super Guru

    Joined:
    Apr 26, 2011
    Messages:
    3,665
    Thanks Received:
    972
    Trophy Points:
    113
    Location:
    North Carolina
    Ratings:
    +1,078 / 0
    Hi again Vistas24 - yes, you did post twice again (or a third time for that matter) - hopefully a moderator will come along and close/combine your redundant posts.

    I will provide a 'simple' solution but one you may not want if you need to have a 'zero' show up for certain days - see the attached pic where I created a small spreadsheet on my iPad (using the latest version of Numbers) - I left the days w/ a '0' blank - the average formula used just the 3 days w/ numbers - if a '0' is included then the average would be based on 5 cells which you do not want.

    Now if you need a complicated formula that will automatically exclude those cells w/ an entered '0' in the average, I cannot help you at the moment - I don't use Numbers much and would need to read up on the iOS 7 version. Good luck and let us know if you indeed find another way? Dave

    Screen Shot 2013-12-29 at 2.41.50 PM.png
     
  3. vistas24

    vistas24 iPF Noob

    Joined:
    Dec 29, 2013
    Messages:
    3
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    las vegas, nv
    Ratings:
    +0 / 0
    Thanks for responding Dave.. I cant leave those zero cells blank. Those cells contain formulas because they are a sum of 2 other columns. I need a formula that automatically excludes those cells.. Thanks so much for your response and sorry for all the multiple posting.. I've never posted in any forum of any kind. I'm just trying to help my wife get a spreadsheet ready for her work...If you figure out a formula or can point me in another direction I would really appreciate it..

    Zach
     
  4. giradman

    giradman iPad Super Guru

    Joined:
    Apr 26, 2011
    Messages:
    3,665
    Thanks Received:
    972
    Trophy Points:
    113
    Location:
    North Carolina
    Ratings:
    +1,078 / 0
    Hi Zach - gotcha you - well that will make for a complicated formula - hopefully, some members w/ more extensive experience w/ Numbers will chime in, and you may want to mention 'which version' of Numbers on the iPad you're running; if you have iOS 7.x on your device then you should have the newest version. Good luck - Dave
     
  5. AQ_OC

    AQ_OC Super Moderator Staff Member

    Joined:
    Apr 28, 2011
    Messages:
    6,411
    Thanks Received:
    431
    Trophy Points:
    83
    Location:
    SC
    Ratings:
    +431 / 0
    Ok...I will delete the other thread I just created. Vistas24, please read the forum rules and do not create multiple posts on the same topic. This is against forum rules. If you have questions, just ask any moderator or member. Many people here are willing to help.
     
    Last edited: Dec 29, 2013
  6. vistas24

    vistas24 iPF Noob

    Joined:
    Dec 29, 2013
    Messages:
    3
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    las vegas, nv
    Ratings:
    +0 / 0
    will do. sorry for breaking the rule. AQ_OC. I'll be on my best behavior....and for any responders, I am running iOS 7 on a brand new Ipad Air..
     
  7. twerppoet

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    17,278
    Thanks Received:
    2,470
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +3,023 / 1
    The AVERAGEIF function should do what you want, or if you need more flexibility the AVERAGEIFS.

    When you browse the functions you can tap the (i) next to them for descriptions and examples.

    When filling out the (condition) field use the text button 'abc'. Don't use the quotation marks. They are automatic.

    If you select the entire column (instead of a range of cells) like I did, make sure you have a footer, or the function will fail as self referencing. Headers and footers are automatically excluded from tests and results performed on entire columns {with a few exceptions}.

    Look under the statistical functions for more variations on the mean, average, etc.

    AverageIf.PNG
     
    • Like Like x 1

Share This Page

Search tags for this page

averageif cell contains a number

,

excel formula that excludes last 2 digits

,

in iwork numbers if value is 0 how can it be blank how can i total

,

iwork numbers formulas