Conditional Formulas--Suppressing error values?

[Arsenio Oloroso]

I've constructed a conditional formula to do one of two alternative
calculations, within a row, in the following standard form: "if A, then B,
else C." Here is my formula, if you're curious:



=IF(ISBLANK(F3),AVERAGE(E3:F3),(E3+F3))



But regardless of which calculation is used, I also want Excel to suppress
any zero or error values that might result. This is so I can properly
average all the calculated values at the bottom of a column.



Yes, in more straightforward conditional formulas, I've used the quotation
marks to create an empty text string in the "answer" cells. This one stumps
me, though.



It seems the form I'm looking for is: "if A, then B, else C-But also D, in
any event."



I appreciate any tips.



Arsenio

 

[RagDyer]

Why would you want to average 2 cells *only* when one of them is blank ? ? ?

 

[Arsenio Oloroso]

I knew someone would ask that. Was hoping no one would.

So, OK...

This is a rather unusual application of a grading sheet for students. The
student gets two chances. If he/she scores within an appropriate range in
the first try, the score is averaged with the second try, which is blank.
Therefore, the averaged grade on the first try remains the same as the first
try.

But if a student fails to score within the appropriate range (gets a low
grade) in that first try, he/she gets a second chance.

In this case, the first try is worth .66 of that first-try score, and the
second try is worth .33. Upon entry of the second grade, the two weighted
grades are added up for the final grade.

Hey...this was my dean's idea! I'm just trying to figure out a way to
install it.

Arsenio

 

[Bob Phillips]

But RD's point I think is that if you have two cells E3 and F3, F3 is blank
then AVERAGE(E3,F3) is the same result as E3, so the AVERAGE is redundant.
So you could easily say

=IF(ISBLANK(F3),E3,(E3+F3))

But furthermore, AVERAGE ignores blanks, so you don't need to test for it.

But following the second detail, I think you want

=IF(ISBLANK(F3),E3,E3*66%+F3*33%)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Arsenio Oloroso]

Thanks, Bob

I see your point about averaging. And as to your second point--weighting
the grades--I've taken care of that in two other columns with conditional
formulas. But my dilemma still remains.

At the bottom of the column where I need to do a running average of grades,
Excel shows zeros where a grade is not yet calculated for a given
assignment. I'm not able to suppress using quote marks because the
conditional formula =IF(ISBLANK(F3),E3,(E3+F3)) doesn't allow me also to
specify that any zero values should be displayed as blanks. Perhaps "piping"
the results into another conditional formula would do the trick, but I don't
know if Excel lets you do that.

 

[Bob Phillips]

How about

=IF(OR(ISBLANK(F3),F3=0),E3,E3+F3)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Arsenio Oloroso]

Incredible! That works!
I'll have to bone up on the OR function.

Thanks much.
Arsenio