Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

#VALUE!

1 view
Skip to first unread message

Donna Newcomb

unread,
Sep 12, 1997, 3:00:00 AM9/12/97
to

Hi all! I really hope that someone can be of assistance to me - because I
am at a loss. I have a spreadsheet with various formulas, which vary only
based on the row in which they are contained. For some reason, one formula
is returning the #VALUE! error. We have attempted reentering all data
which the formula draws from, rewriting the formula, reformatting the cells
to ensure none are formatted as text. Nothing is working. All other
formulas on this sheet work fine. Any help would be greatly appreciated!
Thanks in advance.
[please email response]
--
d...@amherst.com

Robert Rosenberg

unread,
Sep 12, 1997, 3:00:00 AM9/12/97
to

Can you give us some more details? What's the formula?
--
Robert Rosenberg
Microsoft MVP - Excel
----------------

Bill Manville

unread,
Sep 12, 1997, 3:00:00 AM9/12/97
to

On Fri, 12 Sep 1997 06:14:02 -0700, "Donna Newcomb" <d...@amherst.com>
wrote:

>Hi all! I really hope that someone can be of assistance to me - because I
>am at a loss. I have a spreadsheet with various formulas, which vary only
>based on the row in which they are contained. For some reason, one formula
>is returning the #VALUE! error. We have attempted reentering all data
>which the formula draws from, rewriting the formula, reformatting the cells
>to ensure none are formatted as text. Nothing is working. All other
>formulas on this sheet work fine. Any help would be greatly appreciated!

Very difficult to help you if you do not share the formula with us.

Bill Manville
Oxford, England
Microsoft Excel - MVP

Nick Hodge

unread,
Sep 13, 1997, 3:00:00 AM9/13/97
to

Donna

Before we look at the actual workbook, can you give us the formula which is
giving the trouble, as it seems you have covered the obvious

HTH
--
Nick Hodge
Oxford, England

Nick_...@CompuServe.com

Donna Newcomb <d...@amherst.com> wrote in article
<01bcbf7d$4d8f85c0$7201...@dan-95.amherst.com>...


> Hi all! I really hope that someone can be of assistance to me - because
I
> am at a loss. I have a spreadsheet with various formulas, which vary
only
> based on the row in which they are contained. For some reason, one
formula
> is returning the #VALUE! error. We have attempted reentering all data
> which the formula draws from, rewriting the formula, reformatting the
cells
> to ensure none are formatted as text. Nothing is working. All other
> formulas on this sheet work fine. Any help would be greatly appreciated!

DMcRitchie

unread,
Sep 14, 1997, 3:00:00 AM9/14/97
to

Donna,

First the obvious, which you appear to have covered
Help --> topics --> index --> VALUE! error

Since Format --> Cells --> ...
only applies to new material added, it wouldn't tell you much. What you
want to see is how Excel sees what you have. In order to to this the
CELLS worksheet function can be used. Refer to the help, you will need it
interpret the format codes.
Help --> topics --> index --> CELLS worksheet function

I believe CELLS is a add-in. So under Tools --> Add-ins..
Analysis Toolpak should be checked.

Also since my example uses INDIRECT so that you can show the cellname you
want to examine separately.
refer to Help --> topics --> index --> INDIRECT worksheet ... --

I find the following macro installed in a MODULE sheet also very useful.
It is also used in my example:

Function ShowFormula(cell)
ShowFormula = cell.Formula
End Function

My example can be copied then pasted to new worksheet. It is a CSV (comma
separated variable) file and is expanded in Excel using Data --> Text to
columns and comma as the separator

,a,=B1/3,1:01:01,=3/0
,,,0,
,b1,c1,d1,e1,x
address,"=CELL($A4,INDIRECT(B3))","=CELL($A4,INDIRECT(C3))","=CELL($A4,IND
IRECT(D3))","=CELL($A4,INDIRECT(E3))",x
contents,"=CELL($A5,INDIRECT(B3))","=CELL($A5,INDIRECT(C3))","=CELL($A5,IN
DIRECT(D3))","=CELL($A5,INDIRECT(E3))",x
format,"=CELL($A6,INDIRECT(B3))","=CELL($A6,INDIRECT(C3))","=CELL($A6,INDI
RECT(D3))","=CELL($A6,INDIRECT(E3))",x
prefix,"=CELL($A7,INDIRECT(B3))","=CELL($A7,INDIRECT(C3))","=CELL($A7,INDI
RECT(D3))","=CELL($A7,INDIRECT(E3))",x
type,"=CELL($A8,INDIRECT(B3))","=CELL($A8,INDIRECT(C3))","=CELL($A8,INDIRE
CT(D3))","=CELL($A8,INDIRECT(E3))",x
FORMULA,=ShowFormula(INDIRECT(B3)),=ShowFormula(INDIRECT(C3)),=ShowFormula
(INDIRECT(D3)),=ShowFormula(INDIRECT(E3)),x

INDIRECT is used so that you specifiy and see what cell you are examining.
In this example, since cell B3 contains "b1" the following are equivalent:
=CELL("format",b1)
=CELL("format",indirect(b3))

Note in the example #VALUE! error is caused by attempting to divide a
literal by 3.

Hope this helps,

David McRitchie
DMcRitchie

Chris Nelson

unread,
Sep 16, 1997, 3:00:00 AM9/16/97
to

Well, we're not going to be able to help you too much without
an example of your problem. There are ALL SORTS of ways to
create this error! What's doing it in your case, and where?
Feel free to email a copy of the sheet direct, if you don't want
to post it.

0 new messages