+ Reply to Thread
Results 1 to 3 of 3

Array formula to ignore blank cells

  1. #1
    Registered User
    Join Date
    06-04-2008
    Location
    Marietta, GA
    Posts
    18

    Array formula to ignore blank cells

    Hi! I've been perusing a spreadsheet in Excel 2003 I've been working on with lots of array formulas to calculate averages based on two criteria. I thought my formulas were working until I discovered my averages seemed abnormally low. I checked this using a filter on the data and confirmed it.

    I have several cases where there is no data to average for a cell, but the cell is counted since it meets the two if criteria. I believe the array is then counting this as a zero and averaging accordingly.

    Is there an easy way to write the array formula to ignore the blank cells?

    Here, for example, is what I am doing now:

    =AVERAGE(IF(SUMMARY!$AF$2:SUMMARY!$AF$300=$A3,IF(SUMMARY!$BK$2:SUMMARY!$BK$300=$A$2,SUMMARY!BG$2:SUMMARY!BG$300)))

    I want the cell to go to the Summary tab, check column AF and BK and then if the two criteria are met, average the data in cell BG. However, there are one or two rows in BG that are blank in some cases. How do I write the formula to exclude those cells?

    Thanks for your help. I can attach a spreadsheet if necessary.
    Last edited by NBVC; 04-13-2011 at 04:54 PM.
    Bronson Beisel
    Marietta, GA

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Array formula to ignore blank cells

    Try:

    =AVERAGE(IF(SUMMARY!$AF$2:$AF$300=$A3,IF(SUMMARY!$BK$2:$BK$300=$A$2, IF(SUMMARY!BG$2:BG$300<>"",SUMMARY!BG$2:BG$300))))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-04-2008
    Location
    Marietta, GA
    Posts
    18

    Re: Array formula to ignore blank cells

    Bless you! that was exactly what I needed. I did all kinds of searches and was close as I saw several references to the <> addition to the formula, but no idea how to add it.

    You've saved my life. Thank you! I shall give you high marks!

  4. #4
    Registered User
    Join Date
    09-22-2012
    Location
    Cheltenham
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Array formula to ignore blank cells

    Hi!

    I have been trying in vain to figure out how to modify the following array formula in order to make Excel ignore blanks in the averaging:

    =AVERAGE(IF(MOD(ROW(OFFSET(Data,Start-1,0))-ROW(OFFSET(OFFSET(Data,Start-1,0),0,0,1,1)),EveryN)=0,OFFSET(Data,Start-1,0),FALSE))

    The modification below looks very simple but I can't figure out how to apply it to the above formula. I am trying to average every nth data value (specified in cell 'EveryN') in the list 'Data' given a certain start position (specified in cell 'Start').

    Please could you help me out? I would be very grateful for your advice.

    Kind Regards,
    James.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.05568 seconds
  • Memory Usage 8,898KB
  • Queries Executed 14 (?)
More Information
Template Usage (31):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (46):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • bbcode_parse_start
  • postbit_imicons
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1