# Thread: Formula help (AVERAGE function) (Excel 2003)

1. ## 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. ## 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. ## 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. ## 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. ## 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
•