Formula Help
 

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
--
Newfie

Formula Help
 

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

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

"Newfie809" wrote:

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
--
Newfie

Formula Help
 

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


"Sean Timmons" wrote:

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

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

"Newfie809" wrote:

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
--
Newfie

Formula Help
 

--
Newfie


"Newfie809" wrote:

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

Newfie


"Sean Timmons" wrote:

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

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

"Newfie809" wrote:

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
--
Newfie

Formula Help
 

--
Newfie


"Newfie809" wrote:

B C D E F G
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
--
Newfie

Formula Help
 

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


On Oct 23, 4:53*pm, Newfie809 wrote:
--
Newfie

"Newfie809" wrote:
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





Newfie

"Sean Timmons" wrote:

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

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

"Newfie809" wrote:

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

=ROUND((COUNTIF(B3,"<")+COUNTIF(C3,"<")+COUNTIF( G3,"<")+MIN(COUNTIF(D3:F*3,"<"),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
--
Newfie- Hide quoted text -

- Show quoted text -