Excel Calculation Error for formula to sum

[longer]

I entered a formula of =sum(E11:E56) for example and excel did not return a
correct calculation. I checked the format of the cells and it is a custom
one. I checked for hidden values and could not find any (to my understanding
it will show a blank cell but if the cell is highlighted and it is a hidden
value it will show the number in the formula bar). The sum that Excel
returned is exactly 6033 over, which is a value that is included of what I
told excel to add for me. Any ideas? I changed the format to general, to a
number, etc and it did not help. Also, another item that is strange is that
if I copy the cells from the spreadsheet to a new one, it adds fine. I also
checked the style and it is set on common, changed the format of the cells to
general, text, currency, etc and it still will not add correctly. Thank you.

--
Longer

--
Longer

[JulieD]

Hi

it is possible that something that looks like a number isn't a number
you can use (in column F)
=ISNUMBER(E11)
to test whether all the numbers in the range you're adding are actually
numbers
- if not, click on an unused and unformatted cell somewhere and copy it
select your range and choose edit/ paste special - values
you might also need to click on your formula and press F2 F9 and then enter

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"longer" <longer@discussions.microsoft.com> wrote in message
news:A990F402-E6F2-4378-801B-92C0C214DA46@microsoft.com...
>I entered a formula of =sum(E11:E56) for example and excel did not return a
> correct calculation. I checked the format of the cells and it is a custom
> one. I checked for hidden values and could not find any (to my
> understanding
> it will show a blank cell but if the cell is highlighted and it is a
> hidden
> value it will show the number in the formula bar). The sum that Excel
> returned is exactly 6033 over, which is a value that is included of what I
> told excel to add for me. Any ideas? I changed the format to general, to
> a
> number, etc and it did not help. Also, another item that is strange is
> that
> if I copy the cells from the spreadsheet to a new one, it adds fine. I
> also
> checked the style and it is set on common, changed the format of the cells
> to
> general, text, currency, etc and it still will not add correctly. Thank
> you.
>
> --
> Longer
>
> --
> Longer

[Niek Otten]

<select your range and choose edit/ paste special - values>

Don't!

I'm sure Julie meant select your range and choose Edit>Paste Special, check
Add.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:%23YvxDaMTFHA.3264@TK2MSFTNGP15.phx.gbl...
> Hi
>
> it is possible that something that looks like a number isn't a number
> you can use (in column F)
> =ISNUMBER(E11)
> to test whether all the numbers in the range you're adding are actually
> numbers
> - if not, click on an unused and unformatted cell somewhere and copy it
> select your range and choose edit/ paste special - values
> you might also need to click on your formula and press F2 F9 and then
> enter
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ...well i'm working on it anyway
> "longer" <longer@discussions.microsoft.com> wrote in message
> news:A990F402-E6F2-4378-801B-92C0C214DA46@microsoft.com...
>>I entered a formula of =sum(E11:E56) for example and excel did not return
>>a
>> correct calculation. I checked the format of the cells and it is a
>> custom
>> one. I checked for hidden values and could not find any (to my
>> understanding
>> it will show a blank cell but if the cell is highlighted and it is a
>> hidden
>> value it will show the number in the formula bar). The sum that Excel
>> returned is exactly 6033 over, which is a value that is included of what
>> I
>> told excel to add for me. Any ideas? I changed the format to general,
>> to a
>> number, etc and it did not help. Also, another item that is strange is
>> that
>> if I copy the cells from the spreadsheet to a new one, it adds fine. I
>> also
>> checked the style and it is set on common, changed the format of the
>> cells to
>> general, text, currency, etc and it still will not add correctly. Thank
>> you.
>>
>> --
>> Longer
>>
>> --
>> Longer
>
>

[JulieD]

Hi Niek

thanks for this ... don't know where my brain is tonight

--
Cheers
JulieD

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:%23G%23iTjMTFHA.3188@TK2MSFTNGP09.phx.gbl...
> <select your range and choose edit/ paste special - values>
>
> Don't!
>
> I'm sure Julie meant select your range and choose Edit>Paste Special,
> check Add.
>
> --
>
> Kind Regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> news:%23YvxDaMTFHA.3264@TK2MSFTNGP15.phx.gbl...
>> Hi
>>
>> it is possible that something that looks like a number isn't a number
>> you can use (in column F)
>> =ISNUMBER(E11)
>> to test whether all the numbers in the range you're adding are actually
>> numbers
>> - if not, click on an unused and unformatted cell somewhere and copy it
>> select your range and choose edit/ paste special - values
>> you might also need to click on your formula and press F2 F9 and then
>> enter
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ...well i'm working on it anyway
>> "longer" <longer@discussions.microsoft.com> wrote in message
>> news:A990F402-E6F2-4378-801B-92C0C214DA46@microsoft.com...
>>>I entered a formula of =sum(E11:E56) for example and excel did not return
>>>a
>>> correct calculation. I checked the format of the cells and it is a
>>> custom
>>> one. I checked for hidden values and could not find any (to my
>>> understanding
>>> it will show a blank cell but if the cell is highlighted and it is a
>>> hidden
>>> value it will show the number in the formula bar). The sum that Excel
>>> returned is exactly 6033 over, which is a value that is included of what
>>> I
>>> told excel to add for me. Any ideas? I changed the format to general,
>>> to a
>>> number, etc and it did not help. Also, another item that is strange is
>>> that
>>> if I copy the cells from the spreadsheet to a new one, it adds fine. I
>>> also
>>> checked the style and it is set on common, changed the format of the
>>> cells to
>>> general, text, currency, etc and it still will not add correctly. Thank
>>> you.
>>>
>>> --
>>> Longer
>>>
>>> --
>>> Longer
>>
>>
>
>