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.

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.

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.

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.

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.

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.

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.

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.

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.

If you want, you can mail it to me at rmcauctions@NOSPAMhotmail.com without the NOSPAM part. I will take a look and see if I see
your problem.

HTH
--
RMC,CPA


"adodson" <adodson@discussions.microsoft.com> wrote in message news:AF4838C1-504B-46AA-A45A-21DEF5D2289C@microsoft.com...
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.

sent. Thanks in advance.

"R. Choate" wrote:

> If you want, you can mail it to me at rmcauctions@NOSPAMhotmail.com without the NOSPAM part. I will take a look and see if I see
> your problem.
>
> HTH
> --
> RMC,CPA
>
>
> "adodson" <adodson@discussions.microsoft.com> wrote in message news:AF4838C1-504B-46AA-A45A-21DEF5D2289C@microsoft.com...
> 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.
>
>
>