Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
I have a simple formula subtracting one cell from another using =SUM(XX,
-XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
According to your formula, you are subtracting something (XX) from
itself (XX) which will always equal zero. Out of curiosity, why use the SUM function. Isn't it easier to just type the equation such as... = A1 - A2 - John Michl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
hi try =SUM(XX,(-XX) -via135 Excel User Wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=502600 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
sorry! ")" omitted! correct one! =SUM(XX,(-XX)) -via135 via135 Wrote: hi try =SUM(XX,(-XX) -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=502600 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
I did try that and it still return a zero value in the actual cell, although
as before, if I open the function arguments pop up box, the correct value is listed there. Any other ideas why a function result would display correctly in the arguments box but a completely different number display in the cell itself? "via135" wrote: hi try =SUM(XX,(-XX) -via135 Excel User Wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=502600 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
You might get better responses if you post the following
1. Values of Referenced Cells 2. Formula you are using 3. Actual Result 4. Desired Result Example: A1: 34 B1: 12 C1: =SUM(A1-B1) outputs 6, but is supposed to output 22. In which case I would tell you to check the formats of each cell, and ask you why you were using the SUM function. "Excel User" wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
The XX's were just examples and I have tried it the way you suggested below
with the same result: =C60-C238 still displays a $0.00 in the cell although the actual result is $6,709.48 "John Michl" wrote: According to your formula, you are subtracting something (XX) from itself (XX) which will always equal zero. Out of curiosity, why use the SUM function. Isn't it easier to just type the equation such as... = A1 - A2 - John Michl |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
Is there any special number formatting in your cell. Try setting it to
General. Right-click cell, select 'Format Cells'. On the Number tab, set to General. Any change? Paul "Excel User" wrote in message ... I did try that and it still return a zero value in the actual cell, although as before, if I open the function arguments pop up box, the correct value is listed there. Any other ideas why a function result would display correctly in the arguments box but a completely different number display in the cell itself? "via135" wrote: hi try =SUM(XX,(-XX) -via135 Excel User Wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=502600 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
Sounds like a formating issue. Click the cell that doesn't display
correctly then clear the formatting via Edit Clear Formats. If you see the number, try reformatting. - John Michl |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
Format to General as PCLIVE suggests.
However, this by itself will not do the trick because the numbers are text. After formatting to Generql, copy a blank cell then select your other cells and Paste SpecialAddOKEsc. This forces them to numbers. Gord Dibben MS Excel MVP On Wed, 18 Jan 2006 14:55:50 -0500, "PCLIVE" wrote: Is there any special number formatting in your cell. Try setting it to General. Right-click cell, select 'Format Cells'. On the Number tab, set to General. Any change? Paul "Excel User" wrote in message ... I did try that and it still return a zero value in the actual cell, although as before, if I open the function arguments pop up box, the correct value is listed there. Any other ideas why a function result would display correctly in the arguments box but a completely different number display in the cell itself? "via135" wrote: hi try =SUM(XX,(-XX) -via135 Excel User Wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=502600 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
Hi
I'm having exactly the same problem, although with a slightly more complicated formula. The formula is this: SUMIF('July 06'!$K$3:$K$36,'Report'!$A$24,'July 06'!$R$3:$R$36), the idea being to get a sum of column R if the name in A24 appears in column K. The only result this formula is displaying is 0, but in the arguments box it gives the correct answer of 43. adding, subtracting, dividing etc. anything to this cell gives a result of 0, eg formula + 10 will show 0, dividing something by this result, instead of showing div/0 will show 0. I've tried every format available, copied it to different cells etc, but will only display the 0 result. Any ideas other than what's been mentioned? Thanks "Sloth" wrote: You might get better responses if you post the following 1. Values of Referenced Cells 2. Formula you are using 3. Actual Result 4. Desired Result Example: A1: 34 B1: 12 C1: =SUM(A1-B1) outputs 6, but is supposed to output 22. In which case I would tell you to check the formats of each cell, and ask you why you were using the SUM function. "Excel User" wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
I've got EXACTLY the same problem, with a very similar formula:
=SUM(IF('BLOW OUT PREVENTER MD (BOPMD)'!$O$4:$O$289=3,IF('BLOW OUT PREVENTER MD (BOPMD)'!$N$4:$N$289="VISUAL",1,0))) Formula is to count how many items on that worksheet equal both # in column O and "VISUAL" in column N. Odd thing - it works in the table above it, but copying the formula and changing the worksheet name in the formula make it read "0" in the answer, where in the table above it (same formula but calculating a different worksheet) DOES work. And, identically to Aaron's problem, when you look at the formula arguments box it HAS the correct answer - but it only displays in the cell. I've tried re-formatting it 9 ways from Sunday - nothing seems to have any effect. It's driving me crazy - I've got a LOT of these worksheets and calculations to do, and some work, some don't... Wayne "Aaron" wrote: Hi I'm having exactly the same problem, although with a slightly more complicated formula. The formula is this: SUMIF('July 06'!$K$3:$K$36,'Report'!$A$24,'July 06'!$R$3:$R$36), the idea being to get a sum of column R if the name in A24 appears in column K. The only result this formula is displaying is 0, but in the arguments box it gives the correct answer of 43. adding, subtracting, dividing etc. anything to this cell gives a result of 0, eg formula + 10 will show 0, dividing something by this result, instead of showing div/0 will show 0. I've tried every format available, copied it to different cells etc, but will only display the 0 result. Any ideas other than what's been mentioned? Thanks "Sloth" wrote: You might get better responses if you post the following 1. Values of Referenced Cells 2. Formula you are using 3. Actual Result 4. Desired Result Example: A1: 34 B1: 12 C1: =SUM(A1-B1) outputs 6, but is supposed to output 22. In which case I would tell you to check the formats of each cell, and ask you why you were using the SUM function. "Excel User" wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
One more thing I've noticed... in the cells where the formula works, if I
click on the cell then the is surrounded by "{ }", but in the cells where it doesn't work, those brackets are not there. However, if I add those brackets manually to the formula in the cell where it does NOT work, it then displays the formula in the cell, not the result (or even "0") "Wayne" wrote: I've got EXACTLY the same problem, with a very similar formula: =SUM(IF('BLOW OUT PREVENTER MD (BOPMD)'!$O$4:$O$289=3,IF('BLOW OUT PREVENTER MD (BOPMD)'!$N$4:$N$289="VISUAL",1,0))) Formula is to count how many items on that worksheet equal both # in column O and "VISUAL" in column N. Odd thing - it works in the table above it, but copying the formula and changing the worksheet name in the formula make it read "0" in the answer, where in the table above it (same formula but calculating a different worksheet) DOES work. And, identically to Aaron's problem, when you look at the formula arguments box it HAS the correct answer - but it only displays in the cell. I've tried re-formatting it 9 ways from Sunday - nothing seems to have any effect. It's driving me crazy - I've got a LOT of these worksheets and calculations to do, and some work, some don't... Wayne "Aaron" wrote: Hi I'm having exactly the same problem, although with a slightly more complicated formula. The formula is this: SUMIF('July 06'!$K$3:$K$36,'Report'!$A$24,'July 06'!$R$3:$R$36), the idea being to get a sum of column R if the name in A24 appears in column K. The only result this formula is displaying is 0, but in the arguments box it gives the correct answer of 43. adding, subtracting, dividing etc. anything to this cell gives a result of 0, eg formula + 10 will show 0, dividing something by this result, instead of showing div/0 will show 0. I've tried every format available, copied it to different cells etc, but will only display the 0 result. Any ideas other than what's been mentioned? Thanks "Sloth" wrote: You might get better responses if you post the following 1. Values of Referenced Cells 2. Formula you are using 3. Actual Result 4. Desired Result Example: A1: 34 B1: 12 C1: =SUM(A1-B1) outputs 6, but is supposed to output 22. In which case I would tell you to check the formats of each cell, and ask you why you were using the SUM function. "Excel User" wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
Those curly-braces mean the formula in an array-entered formula. This kind
of formula requires you to commit it using Ctrl+Shift+Enter, not just Enter by itself. Select one of the cells without the curly-braces, click in the Formula Bar and then press Ctrl+Shift+Enter... doing this should make the formula work (unless you have made a modification that is incorrect). Once you have an array-entered formula in place, you can copy it down or across normally. Rick "Wayne" wrote in message ... One more thing I've noticed... in the cells where the formula works, if I click on the cell then the is surrounded by "{ }", but in the cells where it doesn't work, those brackets are not there. However, if I add those brackets manually to the formula in the cell where it does NOT work, it then displays the formula in the cell, not the result (or even "0") "Wayne" wrote: I've got EXACTLY the same problem, with a very similar formula: =SUM(IF('BLOW OUT PREVENTER MD (BOPMD)'!$O$4:$O$289=3,IF('BLOW OUT PREVENTER MD (BOPMD)'!$N$4:$N$289="VISUAL",1,0))) Formula is to count how many items on that worksheet equal both # in column O and "VISUAL" in column N. Odd thing - it works in the table above it, but copying the formula and changing the worksheet name in the formula make it read "0" in the answer, where in the table above it (same formula but calculating a different worksheet) DOES work. And, identically to Aaron's problem, when you look at the formula arguments box it HAS the correct answer - but it only displays in the cell. I've tried re-formatting it 9 ways from Sunday - nothing seems to have any effect. It's driving me crazy - I've got a LOT of these worksheets and calculations to do, and some work, some don't... Wayne "Aaron" wrote: Hi I'm having exactly the same problem, although with a slightly more complicated formula. The formula is this: SUMIF('July 06'!$K$3:$K$36,'Report'!$A$24,'July 06'!$R$3:$R$36), the idea being to get a sum of column R if the name in A24 appears in column K. The only result this formula is displaying is 0, but in the arguments box it gives the correct answer of 43. adding, subtracting, dividing etc. anything to this cell gives a result of 0, eg formula + 10 will show 0, dividing something by this result, instead of showing div/0 will show 0. I've tried every format available, copied it to different cells etc, but will only display the 0 result. Any ideas other than what's been mentioned? Thanks "Sloth" wrote: You might get better responses if you post the following 1. Values of Referenced Cells 2. Formula you are using 3. Actual Result 4. Desired Result Example: A1: 34 B1: 12 C1: =SUM(A1-B1) outputs 6, but is supposed to output 22. In which case I would tell you to check the formats of each cell, and ask you why you were using the SUM function. "Excel User" wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My formula returns a result but the cell displays a zero?
Rick,
Thank you EVER SO VERY MUCH!! That worked... I thought I was going to go bald trying to figure it out (pulling my hair out...). I truly appreciate it.... "Rick Rothstein (MVP - VB)" wrote: Those curly-braces mean the formula in an array-entered formula. This kind of formula requires you to commit it using Ctrl+Shift+Enter, not just Enter by itself. Select one of the cells without the curly-braces, click in the Formula Bar and then press Ctrl+Shift+Enter... doing this should make the formula work (unless you have made a modification that is incorrect). Once you have an array-entered formula in place, you can copy it down or across normally. Rick "Wayne" wrote in message ... One more thing I've noticed... in the cells where the formula works, if I click on the cell then the is surrounded by "{ }", but in the cells where it doesn't work, those brackets are not there. However, if I add those brackets manually to the formula in the cell where it does NOT work, it then displays the formula in the cell, not the result (or even "0") "Wayne" wrote: I've got EXACTLY the same problem, with a very similar formula: =SUM(IF('BLOW OUT PREVENTER MD (BOPMD)'!$O$4:$O$289=3,IF('BLOW OUT PREVENTER MD (BOPMD)'!$N$4:$N$289="VISUAL",1,0))) Formula is to count how many items on that worksheet equal both # in column O and "VISUAL" in column N. Odd thing - it works in the table above it, but copying the formula and changing the worksheet name in the formula make it read "0" in the answer, where in the table above it (same formula but calculating a different worksheet) DOES work. And, identically to Aaron's problem, when you look at the formula arguments box it HAS the correct answer - but it only displays in the cell. I've tried re-formatting it 9 ways from Sunday - nothing seems to have any effect. It's driving me crazy - I've got a LOT of these worksheets and calculations to do, and some work, some don't... Wayne "Aaron" wrote: Hi I'm having exactly the same problem, although with a slightly more complicated formula. The formula is this: SUMIF('July 06'!$K$3:$K$36,'Report'!$A$24,'July 06'!$R$3:$R$36), the idea being to get a sum of column R if the name in A24 appears in column K. The only result this formula is displaying is 0, but in the arguments box it gives the correct answer of 43. adding, subtracting, dividing etc. anything to this cell gives a result of 0, eg formula + 10 will show 0, dividing something by this result, instead of showing div/0 will show 0. I've tried every format available, copied it to different cells etc, but will only display the 0 result. Any ideas other than what's been mentioned? Thanks "Sloth" wrote: You might get better responses if you post the following 1. Values of Referenced Cells 2. Formula you are using 3. Actual Result 4. Desired Result Example: A1: 34 B1: 12 C1: =SUM(A1-B1) outputs 6, but is supposed to output 22. In which case I would tell you to check the formats of each cell, and ask you why you were using the SUM function. "Excel User" wrote: I have a simple formula subtracting one cell from another using =SUM(XX, -XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Can I make a formula in Excel to display result in same cell? | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Cell displays formula and not result | Excel Discussion (Misc queries) |