Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula 'mis' calculate when reference cell contains a "0"
This is my third entry today!
I have a data source and a worksheet that is "counting" with sumproduct formulas the number of entries that meet specific criteria. My problem: When I "change" the data source and recalculate the formula, the output changes in unexpected ways. In particular, I can re-enter in the data source a number, the exact same number as was in a cell previously, but the formulas which want to count that number no longer seem to 'recognize' it. For instance, with the following data set source, I want to count the number of entries for which column B equals 0 and column C equals 2 or 3. A B C ------------------------------- 1| entry1 0 2 2| entry2 0 3 3| entry3 0 4 4| entry4 1 2 5| entry5 1 3 6| entry6 1 4 I use a formula like: =SUMPRODUCT($B$1:$B$6=0)*(NOT($C$1:$C$6=4) At first, I was getting the right output: "2" Then, I would fidgit with the source data, say, by deleting the contents of cell B1 ("0"), and re-entering the exact same value ("0"). When I update my sumproduct formula, it gives the output: "1". I notice that if I delete and re-enter the value of C1 ("2"), however, this does not happen. That is, it is only when I delete and re-enter "0" in the data source that the formula seems to not recognize it as "0" anymore. Does anyone know what's going on here? Since this is my third issue today, I'm wondering if Excel gets too "heavy" with formulas after a while and starts to malfunction. Thanks for any help. --Rachel. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula 'mis' calculate when reference cell contains a "0"
My guess is that your column B contains numbers, but the cells are formatted
as text. Thus, your formula recognizes the numbers that are currently stored there, but if you delete and reenter any of the values, the new value will be text. Since your formula is looking for the number 0, it will not recognize the text value 0. Try changing the cell format of your Column B to "General" and see if that fixes the problem. HTH, Elkar "Rachel" wrote: This is my third entry today! I have a data source and a worksheet that is "counting" with sumproduct formulas the number of entries that meet specific criteria. My problem: When I "change" the data source and recalculate the formula, the output changes in unexpected ways. In particular, I can re-enter in the data source a number, the exact same number as was in a cell previously, but the formulas which want to count that number no longer seem to 'recognize' it. For instance, with the following data set source, I want to count the number of entries for which column B equals 0 and column C equals 2 or 3. A B C ------------------------------- 1| entry1 0 2 2| entry2 0 3 3| entry3 0 4 4| entry4 1 2 5| entry5 1 3 6| entry6 1 4 I use a formula like: =SUMPRODUCT($B$1:$B$6=0)*(NOT($C$1:$C$6=4) At first, I was getting the right output: "2" Then, I would fidgit with the source data, say, by deleting the contents of cell B1 ("0"), and re-entering the exact same value ("0"). When I update my sumproduct formula, it gives the output: "1". I notice that if I delete and re-enter the value of C1 ("2"), however, this does not happen. That is, it is only when I delete and re-enter "0" in the data source that the formula seems to not recognize it as "0" anymore. Does anyone know what's going on here? Since this is my third issue today, I'm wondering if Excel gets too "heavy" with formulas after a while and starts to malfunction. Thanks for any help. --Rachel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
CELL Function: cell reference by formula | Excel Worksheet Functions | |||
Cell reference in formula | Excel Worksheet Functions | |||
Copy formula down a column does not use correct cell reference | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |