#value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My spreadsheet has hourly entries and Im using SUMPRODUCT for a hourly total. Somehow my users are changing something that is giving me #value in the total cell. There is no entry and I can go through the cells and delete the blank cells and find the offending cell. However, if I just copy the format from a non-offending cell it does not clear the #value. Both the offending and non-ofending cells are fromatted as GENERAL and if I change the format to NUMBER, it does not clear the error. How can I determine what is causing the #value?
Thanks in advance
 
allan said:
My spreadsheet has hourly entries and Im using SUMPRODUCT for a hourly
Click to expand...
total. Somehow my users are changing something that is giving me #value in
the total cell. There is no entry and I can go through the cells and delete
the blank cells and find the offending cell. However, if I just copy the
format from a non-offending cell it does not clear the #value. Both the
offending and non-ofending cells are fromatted as GENERAL and if I change
the format to NUMBER, it does not clear the error. How can I determine what
is causing the #value?
Thanks in advance
Click to expand...

It may well be that something in a cell (perhaps a space character?) is
causing it to be seen as text. As SUMPRODUCT is expecting numbers, this
gives the #VALUE! error. (A cell doesn't need to be formatted as text for
this.) You can use a formula such as =ISTEXT(A1) copied down to find which
one returns TRUE.
 
Allen
What is the formula that you are using

I would suspect that someone is using the space bar to clear cells. Since the cell looks empty the user is happy, but your formula is seeing the space and can't work

Changing the number format of the cell won't change the fact that it has text entered in it

Next time this comes up try Edit>Find and type in a single space for the Find_what

Good Luck
Mark Graesse
(e-mail address removed)

----- allan wrote: ----

My spreadsheet has hourly entries and Im using SUMPRODUCT for a hourly total. Somehow my users are changing something that is giving me #value in the total cell. There is no entry and I can go through the cells and delete the blank cells and find the offending cell. However, if I just copy the format from a non-offending cell it does not clear the #value. Both the offending and non-ofending cells are fromatted as GENERAL and if I change the format to NUMBER, it does not clear the error. How can I determine what is causing the #value
Thanks in advance
 
Back
Top