Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|