Conditional Formulas--Suppressing error values?
 

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

Conditional Formulas--Suppressing error values?
 

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Arsenio Oloroso" wrote in message
...
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

Conditional Formulas--Suppressing error values?
 

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

"RagDyer" wrote in message
...
Why would you want to average 2 cells *only* when one of them is blank ? ?
?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Arsenio Oloroso" wrote in message
...
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

Conditional Formulas--Suppressing error values?
 

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" wrote in message
...
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

"RagDyer" wrote in message
...
Why would you want to average 2 cells *only* when one of them is blank ?
? ?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Arsenio Oloroso" wrote in message
...
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

Conditional Formulas--Suppressing error values?
 

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" wrote in message
...
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" wrote in message
...
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

"RagDyer" wrote in message
...
Why would you want to average 2 cells *only* when one of them is blank ?
? ?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Arsenio Oloroso" wrote in message
...
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

Conditional Formulas--Suppressing error values?
 

How about

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Arsenio Oloroso" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"RagDyer" wrote in message
...
Why would you want to average 2 cells *only* when one of them is blank
? ? ?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Arsenio Oloroso" wrote in message
...
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

Conditional Formulas--Suppressing error values?
 

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

Thanks much.
Arsenio

"Bob Phillips" wrote in message
...
How about

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Arsenio Oloroso" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"RagDyer" wrote in message
...
Why would you want to average 2 cells *only* when one of them is blank
? ? ?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Arsenio Oloroso" wrote in message
...
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