Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
I have a formula that looks like this:
=(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
Maybe a Change-event macro .......try opening the file without enabeling
macros. Maybe try the same formula in a different cell. FWIW, The numeric versions of your formulas seem work ok in my XL97. Vaya con Dios, Chuck, CABGx3 "adodson" wrote: I have a formula that looks like this: =(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
Thank you for your assistance. There are no macros in this worksheet. I'm
in version 2003. I've tried a different cell and a completely different worksheet and the problem duplicated itself. The only bit of information I can think of that isn't covered, is that some of the numbers are found through a vlookup... Any other thoughts? "CLR" wrote: Maybe a Change-event macro .......try opening the file without enabeling macros. Maybe try the same formula in a different cell. FWIW, The numeric versions of your formulas seem work ok in my XL97. Vaya con Dios, Chuck, CABGx3 "adodson" wrote: I have a formula that looks like this: =(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
Try physically taking each section of the formula and splitting it out into
it's own cell and see if it evaluates as expected. My gut feel is that the problem will be found in G7 or E7 as they are the only ones that would matter if they weren't correct......everything else is just summing. Vaya con Dios, Chuck, CABGx3 "adodson" wrote: Thank you for your assistance. There are no macros in this worksheet. I'm in version 2003. I've tried a different cell and a completely different worksheet and the problem duplicated itself. The only bit of information I can think of that isn't covered, is that some of the numbers are found through a vlookup... Any other thoughts? "CLR" wrote: Maybe a Change-event macro .......try opening the file without enabeling macros. Maybe try the same formula in a different cell. FWIW, The numeric versions of your formulas seem work ok in my XL97. Vaya con Dios, Chuck, CABGx3 "adodson" wrote: I have a formula that looks like this: =(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
We need to imagine what could be wrong to make the formula work yet yield zero?
I would first check E7. If this single cell were zero instead of 6.51%, we would get what you are seeing. The next thing to check is the formatting of the cell; certain formats could make the result look like zero. -- Gary's Student "adodson" wrote: Thank you for your assistance. There are no macros in this worksheet. I'm in version 2003. I've tried a different cell and a completely different worksheet and the problem duplicated itself. The only bit of information I can think of that isn't covered, is that some of the numbers are found through a vlookup... Any other thoughts? "CLR" wrote: Maybe a Change-event macro .......try opening the file without enabeling macros. Maybe try the same formula in a different cell. FWIW, The numeric versions of your formulas seem work ok in my XL97. Vaya con Dios, Chuck, CABGx3 "adodson" wrote: I have a formula that looks like this: =(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
Your description is not very clear. For instance, when you remove /G7*E7
from your formula, do you get 105232 or 0? The usual cause of SUM formulas returning zero is text cells that only look like numbers. What do you get from =COUNT(F5:F33) ? Also does =COUNTIF(H5:H33,"to "&B7) return what you would expect? Jerry "adodson" wrote: I have a formula that looks like this: =(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
Accounting format. the other cells are not 0, they have numbers in them.
"Gary''s Student" wrote: We need to imagine what could be wrong to make the formula work yet yield zero? I would first check E7. If this single cell were zero instead of 6.51%, we would get what you are seeing. The next thing to check is the formatting of the cell; certain formats could make the result look like zero. -- Gary's Student "adodson" wrote: Thank you for your assistance. There are no macros in this worksheet. I'm in version 2003. I've tried a different cell and a completely different worksheet and the problem duplicated itself. The only bit of information I can think of that isn't covered, is that some of the numbers are found through a vlookup... Any other thoughts? "CLR" wrote: Maybe a Change-event macro .......try opening the file without enabeling macros. Maybe try the same formula in a different cell. FWIW, The numeric versions of your formulas seem work ok in my XL97. Vaya con Dios, Chuck, CABGx3 "adodson" wrote: I have a formula that looks like this: =(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
Sorry for the bad description, thank you for your assistance.
When I use the evaluate formula option, it shows the full calculation steps all the way down to 105232 and then it just jumps to 0. "Jerry W. Lewis" wrote: Your description is not very clear. For instance, when you remove /G7*E7 from your formula, do you get 105232 or 0? The usual cause of SUM formulas returning zero is text cells that only look like numbers. What do you get from =COUNT(F5:F33) ? Also does =COUNTIF(H5:H33,"to "&B7) return what you would expect? Jerry "adodson" wrote: I have a formula that looks like this: =(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
If you have 2003, I can email you the spreadsheet to look at.
"Jerry W. Lewis" wrote: Your description is not very clear. For instance, when you remove /G7*E7 from your formula, do you get 105232 or 0? The usual cause of SUM formulas returning zero is text cells that only look like numbers. What do you get from =COUNT(F5:F33) ? Also does =COUNTIF(H5:H33,"to "&B7) return what you would expect? Jerry "adodson" wrote: I have a formula that looks like this: =(F7+SUMIF(H5:H33,"to "&B7,F5:F33))/G7*E7 and reads like this: =(79,129+17,359+1,679+2,081+4,987)/106.51%*6.51% When I go through the evaluate formula, all of the amounts come up correct until the very final =98800.1126654774*0.0651 It then results to 0. I can remove the G7*E7 section of the formula and it will go: (79129+26103) = 105232 = 0 Any ideas as to what is causing it to do this or how I can stop it? I've tried creating a new worksheet to clear any random formatting, and it results in the same response. Thanks in advance for any assistance. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula resulting in 0
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Unable to set the Formula property of the Series class" with a tw | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
HOW TO: In a formula, how to change the format of the resulting te | Excel Worksheet Functions | |||
My formula show up in the cell instead of a resulting value | Excel Worksheet Functions | |||
Formula for comparing 3 numbers and resulting in the lowest of the | Excel Worksheet Functions |