Formula resulting in 0

[Guest]

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.

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Accounting format. the other cells are not 0, they have numbers in them.

 

[Guest]

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.

 

[Guest]

If you have 2003, I can email you the spreadsheet to look at.

 

[R. Choate]

If you want, you can mail it to me at (e-mail address removed) without the NOSPAM part. I will take a look and see if I see
your problem.

HTH

 

[Guest]

sent. Thanks in advance.

If you want, you can mail it to me at (e-mail address removed) without the NOSPAM part. I will take a look and see if I see
your problem.

HTH
--
RMC,CPA


If you have 2003, I can email you the spreadsheet to look at.