#VALUE! in Excel, can not figure out why.

[egptech]

I have a spread sheet with the following columns:

A(Date) Formatted as Date;
B(Total Dividends) Formatted as curency with a constant value of $3.45;
C(Liabilities) Formatted as curency;
D(Shares) Formatted as number, 3 decimal places with a constant value of 38.880;
E(Current Price) Formatted as curency, with a variable value entered monthly;
F(Market Value) Formatted as curency containing formula {=IF(D9*E9,D9*E9,"&quot};
G(Total Acc't. Value) Formatted as curency containing formula {=IF(F9+B9,F9+B9,"&quot};
H(Gain/Loss) Formatted as curency containing formula {=IF(G10>0,G10-G9,"&quot};
I(Total Gain/Loss) Formatted as curency containing formula {=IF(F10>0,IF(G10-4859.26,G10-4859.26,"&quot,"&quot}

The problem shows up in column G which displays #VALUE! if no value is entered in col. E. It should display nothing (NULL). H & I display the same error. As soon as a number is entered in Col. E the correct values appear. It behaves as if it is attempting an operation involving a multiplication or division with zero but the value of the cell should be null, not zero.

I hope someone can shed some light on this.
Gene

 

[PeterMoran]

Hi egptech,

I changed your formulas in cols F and G as below:

Col F: =IF(E2<>&quot;&quot;,D2*E2,&quot;&quot

Col G: =IF(F2<>&quot;&quot;,F2+B2,&quot;&quot

This eliminates the #Value errors, and I think it still does what you want.

Hope this Helps.

Peter Moran
Two heads are always better than one!

 

[TonyJollans]

Hi egptech,

When you reference a blank cell, Excel is smart enough to understand what you want and, in effect, treat it as zero. But you are not referencing a blank cell - your cell in column F contains a formula which returns a string (albeit a zero-length string) and is therefore text which is what causes #VALUE to be returned in column G.

If you change the formulas in columns F and G to return 0 (numeric) instead of “” all should work ‘correctly’ but you will have a lot of zeroes which you don’t want to see. Give all the affected cells a custom format of “#” which hides insignificant zeroes and your zeroes should vanish leaving only the ‘real’ numbers in view.

Enjoy,
Tony

 

[egptech]

Peter,
You hit it exactly! I knew it was referencing a different type of value but could not figure out how to avoid it. I guess thats part of having to teach myself.
Thaks for the solution,
Gene