strange error in formula

[iffi]

i was doing very simple plus minus, like this

=D4-D5-D6-D7+C9

BUT it was giving error of like this #VALUE, i m unable to understand why it is showing such behavior , any idea ?

 

[Jon von der Heyden]

At least one of those cells contains a text value.

 

[kidwispa]

i was doing very simple plus minus, like this

=D4-D5-D6-D7+C9

BUT it was giving error of like this #VALUE, i m unable to understand why it is showing such behavior , any idea ?

It's because one of the values in the cells specified isn't a number...

 

[iffi]

IC, if i select the whole range and change the format to number will solve the problem ? i will try it

 

[Jonmo1]

IC, if i select the whole range and change the format to number will solve the problem ? i will try it

No it will not...

Formatting a cell only changes how the value in the cell is displayed.
The underlying value in the cell remains unchanged.

You need to find which value is not a number.

Use ISNUMBER to test.

=ISNUMBER(D4)
repeat for each value in your formula
Which one(s) return FALSE?

 

[Jon von der Heyden]

What answer do you get from this?

=SUMPRODUCT(--ISNUMBER(D4:D7)+1,{1000;100;10;1})+(ISNUMBER(C9)*10000)

 

[iffi]

No it will not...

Formatting a cell only changes how the value in the cell is displayed.
The underlying value in the cell remains unchanged.

You need to find which value is not a number.

Use ISNUMBER to test.

=ISNUMBER(D4)
repeat for each value in your formula
Which one(s) return FALSE?

i m at home at the moment, i m having this problem at my office. suppose if any of them return FALSE, then what i need to do to solve this problem ? i m not permitted to use internet at office, otherwise it would be easy

 

[iffi]

What answer do you get from this?

=SUMPRODUCT(--ISNUMBER(D4:D7)+1,{1000;100;10;1})+(ISNUMBER(C9)*10000)

sorry sir, as i said above, i can't test this at the moment, i m having this problem at office computer, what should i expect from this formula and then what i need to do to solve my problem after looking at the result of this formula, thanks

 

[Jon von der Heyden]

sorry sir, as i said above, i can't test this at the moment, i m having this problem at office computer, what should i expect from this formula and then what i need to do to solve my problem after looking at the result of this formula, thanks

If I alter that formula slightly to make the results a little more obvious:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style="text-align: right;;">21212</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--ISNUMBER(<font color="Red">D4:D7</font>)+1,{10000;1000;100;10}</font>)+(<font color="Blue">ISNUMBER(<font color="Red">C9</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />

D4 represents the 1st digit, D5 the 2nd, D6 the 3rd, D7 the 4th and C9 the 5th.

In my example the 1's are seen as text values (i.e. D5 and D7). Test this on your sheet tomorrow.

Are you having trouble with only the one expression?

 

[iffi]

all the cells contains number like this for example 1000, 1500,400 etc
let me explain more, basically, i have linked sheet1 with sheet2, whatever data is input in sheet2, same data is taken in sheet1.
for example. in sheet1 D4 CELL = sheet2!D4 then in sheet1 when i do plus n minus of this example range , i get error #VALUE

=D4-D5-D6-D7+C9</pre>