Formula question

[Rufus 2006]

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.

 

[farnuckl]

Hello, welcome to the board!

Try AVERAGEA() it should ignore the blanks.

-farnuckl

 

[lenze]

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]

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.

Lenze- Your solution worked great!

Again both of you, thanks for the big help.

Rufus