=sum #value! error

[TanaMary]

I did a spread sheet to track payments and the percentages of those payments
bi-monthly over a 12 month period. There are 6 percentage columns total for
each entry. It's a progressive formula as we need to review the percentages
bi-monthly. Thus, this is the formula I used in the first set of cells:

=sum(E3+G3+I3+K3+M3+O3)/(D3*12)

After making sure the values worked properly, I copied the formula down the
columns (we have 102 entries so far). When nothing is entered in column E,
the sheet shows #DIV/O!, which is fine. As soon as values are added, it
shows 0% in all the percentage columns.

I noticed today that the last three columns for just one of the cell lines
shows "#VALUE!"; the first three columns for the same line are fine. I
checked the formula both below it and above it and they are identical except
for the cell number. I tried deleting the formulas in those cells and then
duplicating them again by a copy down the column, but still get the same
error and I can't figure out where the error is???

HELP!!!

KM

 

[Peo Sjoblom]

First question is why are you using both SUM and operands like +?

If you use

=SUM(E3,G3,I3,K3,M3,O3)/D3*12

The reason you get value error is that at least one of the cells is text.

Try


=ISTEXT(E3)

and so on to test all the cells

if it returns TRUE it is text

--


Regards,


Peo Sjoblom

 

[TanaMary]

I'm not as proficient in Excel as I would like to be but was asked to do up
the spread sheet; hence the combinations I used.

I will try the test in each cell.

Thank you

 

[TanaMary]

Nope....that's not it....tried all the cells (and even reentered the number
values in the cells I'm calculating)

 

[Peo Sjoblom]

Try this in one cell

=E3+G3+I3+K3+M3+O3


and this in another


=D3*12

which of those 2 cells return the value error?



--


Regards,


Peo Sjoblom

 

[Dave Peterson]

What do you get with this formula:

=count(E3,G3,I3,K3,M3,O3,D3)

If all the cells have real numbers in them, you should see 7. If you see any
other result, then either you have empty cells or you have non-numeric data in
at least one of those cells.

And just reentering the value isn't enough.
Try formatting the cells as General (or Number)
then reenter the values.

Then check your formula.

ps. Peo changed your formula to something that you may not want.

Add those ()'s in the divisor.

=SUM(E3,G3,I3,K3,M3,O3)/(D3*12)

 

[TanaMary]

The first string returns the #value error. The second give me a correct value.

 

[TanaMary]

Okay...one of the cells included in the equation was blank, so I filled it
with a $0. It corrected that row, but I'm still confused because I have
blank cells in other rows, but the cells in those are not returning a #value
error.

The way the spreadsheet has been working (and for all I know that cell has
been like that all along and I just didn't notice) is that, when I enter in
the fixed figure that the rest of the equations will be based on, it turns
ALL of those cells to 0%. Here is a piece of the puzzle:
Mo Order Mth 1&2 %Pd Mnth 3&4 %Pd Mnth 5&6 %Pd
$206.00 $233.00 56.6% $412.00 78.3% $510.00 93.4%
$101.00 $0.00 0.0% $18.70 4.6% 3.1%
$215.00 $0.00 0.0% 0.0% 0.0%
$251.34 $546.45 108.7% $694.35 123.4% $241.35 98.3%
$245.55 $0.00 0.0% $600.00 61.1% $581.52 80.2%
$234.00 $0.00 0.0% $0.00 0% $0.00
#Value!


Not that it really matters anymore because it's correct now, but I guess I'm
not understanding why THAT particular blank cell was returning an error in
those columns. Just so I understand for the future.

Anyway, thanks both to you and Peo for your help!!

 

[Dave Peterson]

Maybe that cell that looked blank wasn't really empty.

It could have have a space character in it--or it could have had the result of a
formula (="").
This formula in A2:
=if(a1="ok",9,"")
would look empty if a1 contained "x"

But that string (="") would break the addition (+) operation.

But =sum() is much more forgiving (if you don't include those +'s!), it'll
ignore text.

 

[Peo Sjoblom]

Maybe the blank was a space in this cell or a "" from an IF formula?

--


Regards,


Peo Sjoblom