#VALUE!

[Mayur]

This error gives me a #VALUE! error when I drag it to other cells that don't
have data. This formula calulates data by month. My spreadsheet has data in
there for Jan and the data calculates fine. But when I drag it to the other
eleven months it gives me this error which i can't have because i print it
out and post it. I need it to be blank. I have two formulas one that
calculates multiple rows wich is this one below:

=IF(ISERROR(VLOOKUP($B$3&$A24,Data!$B$2:$M$7246,6,FALSE)),"",VLOOKUP($B$3&$A24,Data!$B$2:$M$7246,6,FALSE))+(IF(ISERROR(VLOOKUP($B$4&$A24,Data!$B$2:$M$7246,6,FALSE)),"",VLOOKUP($B$4&$A24,Data!$B$2:$M$7246,6,FALSE)))+(IF(ISERROR(VLOOKUP($B$5&$A24,Data!$B$2:$M$7246,6,FALSE)),"",VLOOKUP($B$5&$A24,Data!$B$2:$M$7246,6,FALSE)))

Than I have this formulas that only calulates one row and doesn't show the
error in other rows:

=IF(ISERROR(VLOOKUP($B$3&$A22,Data!$B$2:$M$7246,6,FALSE)),"",VLOOKUP($B$3&$A22,Data!$B$2:$M$7246,6,FALSE))

But I need to use the first one. SO I NEED HELP WITH HOW TO MAKE THE ERROR
GO AWAY AND STILL CALCULATE BY MONTH.

This is so simple but I can't figure it out!!!!! PLEASE HELP!!!

 

[T. Valko]

The problem is being caused when your nested IFs return "" and then you're
trying to sum those.

any number + "" = #VALUE!
"" + "" = #VALUE!

Replace the "" with zeros.

 

[Mayur]

That did work. But I what if I don't want anything in the cells? (not even a
zero or -)

Thank you for your promptness.

 

[T. Valko]

One way...

Replace the "" in the formula with zeros then use a custom number format.

Format>Cells>Number tab>Custom

Where it says Type:, enter:

General;General;;@

OK out

The cell will still contain a 0 but it won't be displayed.

 

[Mayur]

THANK YOU SO MUCH! This doesn't affect my formulas right?

 

[T. Valko]

This doesn't affect my formulas right?

Would a cell that contains a 0 affect any downstream calculations?

 

[francis]

Hi Biff

Would you explain how does this works?
Any where that I can find examples on this?

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[T. Valko]

When applying custom formats there are 4 categories that you can manipulate.
They are:

positve numbers
negative numbers
0
text

The syntax lists the categories separated by a semi-colon:

positive numbers;negative numbers;0;text

The format I suggested:

General;General;;@

General is the positive number format. This means apply no special format to
positive numbers.

General is the negative number format. This means apply no special format to
negative numbers.

The 0 format is blank. That means any 0 values are not to be displayed.

@ is the text format. This is a generic format that means the same as
General or, apply no special format to text.

For more info look in Excel help for Number Format Codes.

John Walkenbach has a sample file that demonstrates a bunch of custom formats:

http://spreadsheetpage.com/index.php/file/custom_number_formats/

John McGimpsey shows how to use custom formatting to color fonts up to 6
conditions without using conditional formatting:

http://mcgimpsey.com/excel/conditional6.html

 

[Mayur]

YOur right that zero doesn't affect it. But now my number don't have commas
or my numbers aren't rounded. I do not want to have to change the formating
every month. I would like to paste in the data and have each tab calculate
and format without any additonal work.

I don't want dollar signs just commas with whole numbers.

 

[Mayur]

I have this formula:

=IF(ISERROR(B13+B14+B15),"",(L13+L14+L15)/(B13+B14+B15)*30) &
=IF(ISERROR(B13+B14+B15),"",C13-(F13+F14+F15)/(B13+B14+B15)*30)

How do I make the #VALUE! error go away for these two. I have tried
changing "" to zeros, it didn't work.

 

[T. Valko]

Well, that's additional formatting!

OK, try this method...

It sounds like you want the numbers formated as NUMBER 2 decimal places and
use the thousands separator.

So, reset the format to NUMBER 2 decimal places with thousands separator.

To hide the zeros...

Select the cell(s) with this formula. Let's assume this is cell A1.
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box on the right:

=A1=0

Click the Format button
Select the Font tab
Set the font color to be the same as the cell background color
OK out