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

"T. Valko" wrote:

One way...

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

FormatCellsNumber tabCustom

Where it says Type:, enter:

General;General;;@

OK out

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

--
Biff
Microsoft Excel MVP


"Mayur" wrote:

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Mayur" wrote:

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,F ALSE))+(IF(ISERROR(VLOOKUP($B$4&$A24,Data!$B$2:$M$ 7246,6,FALSE)),"",VLOOKUP($B$4&$A24,Data!$B$2:$M$7 246,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,F ALSE))

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!!!