Formula Help

[Newfie809]

A B C D E F
ENG4C1 ENG3U1 ENG3U1 0 0 0

=ROUND((COUNTIF(B3,"<>")+COUNTIF(C3,"<>")+COUNTIF(G3,"<>")+MIN(COUNTIF(D3:F3,"<>"),1))/3,2 )

This formula adds the columns B=.333, C=.333, D E F=.333 & G=.333 for a
total of 1.33 and I would like it to be 1.0
I am trying to add the columns that have a code in them and the ones that
have a 0 should not be added. Each cell has a lookup formula that returns
the information from another workbook and if there is no information for the
formula in the other workbook it returns a zero and therefore if there is
something in the cell it get added to the total and I do not want it added to
the total.
What I am looking for is the formula to only count the cells that have a
code in them. If there is a 0 there I do not want the cell to add to the
formula.
Is this possible? I tried a COUNTA but that did not work.

Thank you

 

[Sean Timmons]

so, if they always return text unless there is no value, you can do:

=SUMPRODUCT(--(ISTEXT(B3:G3)))/3

 

[Newfie809]

Hi Sean,

I copied your formula and it still returns a 1.33. Thanks for the help, I
will keep working on it, if there is a will there is a way.


Newfie

 

[Newfie809]

--
Newfie

Hi Sean,

I used your formula and it returned a 0 in the Total Column, I wanted it to return a 1.0 not a 0 because the other cells had codes in them that I wanted to caculate as a .33 for each cell that has a code. The formula in the cell is a lookup and bringing this information in from another workbook. I will keep working on it. There has to be a formula that can do this caculation, or maybe I can use my original formula and have a macro to remove all zero's once the information have been imported from the other worksheet.

CelL B had a code that should have returned .33, Cell C had a code that
should have returned a .33 the the cells DEF are use as .33 and the Cell G is
a zero should have returned zero for a total of 1.00 (I also had round at the
beginning of my formula)

Thanks for your help

 

[Newfie809]

--
Newfie

 

[Pete_UK]

I think this is what you are trying to do:

=ROUND(MIN(1,SUMPRODUCT(--(ISTEXT(D3:F3))))/3+(IF(ISTEXT(B3),1,0)+IF
(ISTEXT(C3),1,0)+IF(ISTEXT(G3),1,0))/3,2)

Hope this helps.

Pete