# Formula question

#### Rufus 2006

##### Board Regular
I have a formula I that I use in a spreadsheet and I don't want it to return a zero. Example: A1=75%, A3=2. B1=80%, B3=2. The formula is: =If(iserror(sum(A1*A3+B1*B3)/4),"",(sum(A1*A3+B1*B3)/4)) . The 4 is from A3+B3. The answer would appear in D2.

Everything is fine as it returns 77.5%. I have this formula in a column and then average the column using the average function. If there is no data entered in A1 to B3 it leaves D2 blank and doesn't average it in. However, if I enter data only in A3 and B3, D2 show 0.00% and this screws up my average caluculation.

Is there a way to to keep D2 from entering 0.00%?

I hope this is clear. If it isn't please let me know what I need to do to make it clearer. I am new to this board and haven't quite figured everything out yet.

Thank you for any help you might be able to give me.

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### farnuckl

##### Board Regular
Hello, welcome to the board!

Try AVERAGEA() it should ignore the blanks.

-farnuckl

#### lenze

##### Legend
Maybe

If(If(iserror(sum(A1*A3+B1*B3)/4),"",(sum(A1*A3+B1*B3)/4))=0,"",If(iserror(sum(A1*A3+B1*B3)/4),"",(sum(A1*A3+B1*B3)/4)))

lenze

#### Rufus 2006

##### Board Regular
Farnuckl, and Lenze:

Thank you both very much for the quick replies. Sorry it took me so long in responding, but I had to go to work and just now got back to working on this.

Franuckl- The AVERAGEA solution didn't work. It actually included the blank spaces and made the average percentage less than what it was before.

Again both of you, thanks for the big help.

Rufus

Replies
1
Views
186
Replies
14
Views
347
Replies
5
Views
101
Replies
3
Views
171
Replies
6
Views
156