Formula question

Rufus 2006

Board Regular
Joined
Dec 15, 2005
Messages
104
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.
 

Some videos you may like

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
Joined
Dec 16, 2003
Messages
127
Hello, welcome to the board!

Try AVERAGEA() it should ignore the blanks.

-farnuckl
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Joined
Dec 15, 2005
Messages
104
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,056
Messages
5,569,951
Members
412,299
Latest member
agentless
Top