What's new
Apple iPad Forum 🍎

Welcome to the Apple iPad Forum, your one stop source for all things iPad. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

Formula for NUMBERS??

vistas24

iPF Noob
Joined
Dec 29, 2013
Messages
3
Reaction score
0
Location
las vegas, nv
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..
 

giradman

iPad Fan
Joined
Apr 26, 2011
Messages
8,096
Reaction score
8,717
Location
North Carolina
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
 
OP
V

vistas24

iPF Noob
Joined
Dec 29, 2013
Messages
3
Reaction score
0
Location
las vegas, nv
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
 

giradman

iPad Fan
Joined
Apr 26, 2011
Messages
8,096
Reaction score
8,717
Location
North Carolina
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..

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
 

AQ_OC

iPF Novice
Joined
Apr 28, 2011
Messages
6,415
Reaction score
435
Location
SC
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:
OP
V

vistas24

iPF Noob
Joined
Dec 29, 2013
Messages
3
Reaction score
0
Location
las vegas, nv
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..
 

twerppoet

iPad Fan
Joined
Jan 8, 2011
Messages
24,132
Reaction score
15,339
Location
Milton-Freewater, OR
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
 

Most reactions

Latest posts

Top