# Thread: Excel 3-D IF Statements

1. =('belt 4'!C3+'Belt 3'!C4+'Belt 3'!C3+'Belt 3'!C12+'Belt 3'!C13)/5

These are being pulled from 2 different sheets, added then divided to get the average. problem is that sometimes the data isn't a number (99.5, 99.3, 97.9, OFF, 84.5) - So I want to exclude the OFF or convert it into a 0 with the formula.

2. Try using the AVERAGE function - it will disregard text

3. Originally Posted by Catharine Richardson - WebGenii
Try using the AVERAGE function - it will disregard text
No it still returns the #VALUE error

4. Ignoring or converting to zero will give entirely different answers. Ignoring will divide by the sum by 4 convering to zero will divide by 5.

Steve

5. Did you change the formula to:

Code:
`=AVERAGE('Belt 4'!C3,'belt 3'!C4,'belt 3'!C3,'belt 3'!C12,'belt 3'!C13)`
I'm guessing you have
Code:
`=AVERAGE('Belt 4'!C3+'belt 3'!C4+'belt 3'!C3+'belt 3'!C12+'belt 3'!C13)`

6. Originally Posted by sdckapr
Ignoring or converting to zero will give entirely different answers. Ignoring will divide by the sum by 4 convering to zero will divide by 5.

Steve
I want it to ignore the text and only factor in the numbers and divide the sum in my example by 4 if one is a text value.

7. Originally Posted by Excel Paul
I want it to ignore the text and only factor in the numbers and divide the sum in my example by 4 if one is a text value.
Then using:
Code:
`=AVERAGE('Belt 4'!C3,'belt 3'!C4,'belt 3'!C3,'belt 3'!C12,'belt 3'!C13)`
will work.

#### Posting Permissions

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