Help in writing a formula

[Melody]

A B C D
1 2.0 1.0 U 3.5
2 2.3 2.0 3.1
3 2.5 1.3 I 2.5
4 =Avg(A1:A3)
I have made columns B & C separate even though it is one entry to distingish
between text and a number. I would like to perform an operation on a number
only if there is not a letter beside it OR only if there is a certain letter
beside it. How would I write: Sum of all numbers in column B (without a
letter next to it in column C OR with the letter I next to it in column C)
and then divide the result by the number of entries that were summed and
multiply by 100? Was I correct to put the "Letters" in column C or could they
have been in column B as well?
Thank you--
Melody

 

[Earl Kiosterud]

Melody,

You were definitely wise to keep the letters separate.

The formula for the sum of those column B cells that meet your criteria is:
=SUMIF(C1:C3,"",B1:B3)+SUMIF(C1:C3,"I",B1:B3)

For the count of the cells that meet your criteria:
=COUNTIF(C1:C3,"")+COUNTIF(C1:C3,"I")

When you combine them into a single formula that does it all, it gets a little messy. Such
a formula is error-prone, and difficult to maintain, giving additional strength to the adage
about spreadsheets being error-prone.

=((SUMIF(C1:C3,"",B1:B3)+SUMIF(C1:C3,"I",B1:B3))/(COUNTIF(C1:C3,"")+COUNTIF(C1:C3,"I")))*100

Sometimes it's better to put the first two formulas in separate cells, then have a third
formula do the division, and the multiplying by 100. Then you can hide the intermediate
results cells if you wish. If the first two were in F14 and F15, the final formula would
be:
=(F14/F15)*100

Actually, when I built the combined gonzo formula, I first built the formulas in F14 and
F15, then pasted them into the third (final) formula in place of the F14 and F15 references,
adding the parentheses. Then I could get rid of F14 and F15.