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.

"T. Valko" wrote:

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

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...umber_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


--
Biff
Microsoft Excel MVP


"francis" wrote:

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