#VALUE!....MAKE IT GO AWAY!

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21
ok so all my formulas are correct but this "#VALUE!" shows in the cells where no data has been selected or inputed.. is there a way to "hide" the error code? its saying theres an error cause there is no data for the formula to calculate.. help anyone?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
you can change your formula to allow for error handling.

e.g

=If(cell_ref="","",your_formula)

replacing the italicized parts with your info.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
You need to replace the "cell_ref" with an actual cell reference, Like B6 (if that's the one to check if any input has been done).

e.g.

=IF(B6="","",B6*I6)

but is it B6 or I6 giving you the problem, because B6*I6 should give 0 if any are blank....unless you have a true space, then it will give #Value

if that is the case, use

=IF(TRIM(B6)="","",B6*I6))


if you need to check both B6&I6, then

=IF(OR(TRIM(B6)="",TRIM(I6)=""),"",B6*I6))
 

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21

ADVERTISEMENT

YOUR AWSOME! THNAKS!
it worked perfectly!!
 

big_mac

Board Regular
Joined
Jul 21, 2006
Messages
167
makes thing a little longer but how about

=if(iserror(B6*I6),"",B6*I6)
 

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21

ADVERTISEMENT

i spoke to soon!! ok so now the error is gone but once the data is there to calculate, the feild stays blank or $ -
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
If B6*I6 on it's own works, then so should the formula when both fields are occupied with a value.
 

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21
my dumb mistake, i hade a "I" when it should of been an "L" hey another question.
how can i protect certain coloums?
 

Forum statistics

Threads
1,136,348
Messages
5,675,238
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top