Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula help (AVERAGE function) (Excel 2003)

    I am trying to use the average function on non-contiguous values in multiple spreadsheets (yielding one answer). But I want exclude any value that is zero. I know you can do this using contiguous values in one spreadsheet. But it is not working for me using non-contiguous values in multiple spreadsheets. I am using the AVERAGE and IF functions, and trying to enter it as an array formula. Does anyone know if what I am trying to do even possible? Please help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula help (AVERAGE function) (Excel 2003)

    Try assembling a sheet with formulas that link to the various sheets, you can then only need a formula that operates on that sheet.

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula help (AVERAGE function) (Excel 2003)

    I am using a formula that is adding in the values from all of the spreadsheets I have involved - here is an example of it - =(+Q28+JAN!V28+FEB!V28+MAR!V28+APR!V28+MAY!V28+JUN !V28+JUL!V28+AUG!V28+SEP!V28+OCT!V28+NOV!V28)/12
    As you can see I have a sheet for each month of the year. I want to be able to average those values, but exclude the values that are 0. Can I do that using the AVERAGE and IF functions?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula help (AVERAGE function) (Excel 2003)

    Put links to JAN!V28, FEB!V28 etc. on one sheet, for example in A30:A41, and calculate the average of those cells in an array formula:

    <table border=1><td></td><td align=center>A</td><td align=center>30</td><td>=JAN!V28</td><td align=center>31</td><td>=FEB!V28</td><td align=center>32</td><td>=MAR!V28</td><td align=center>33</td><td>=APR!V28</td><td align=center>34</td><td>=MAY!V28</td><td align=center>35</td><td>=JUN!V28</td><td align=center>36</td><td>=JUL!V28</td><td align=center>37</td><td>=AUG!V28</td><td align=center>38</td><td>=SEP!V28</td><td align=center>39</td><td>=OCT!V28</td><td align=center>40</td><td>=NOV!V28</td><td align=center>41</td><td>=DEC!V28</td><tr><td align=center>42</td><td align=right>

  5. #5
    New Lounger
    Join Date
    Jun 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula help (AVERAGE function) (Excel 2003)

    You're an obvious genius. Thank you so much.

Posting Permissions

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