| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
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!!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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!!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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!!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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!!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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!!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
This doesn't affect my formulas right?
Would a cell that contains a 0 affect any downstream calculations? -- Biff Microsoft Excel MVP "Mayur" wrote: 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!!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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!!! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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!!! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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!!! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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. "Mayur" wrote: 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!!! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
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 FormatConditional 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 -- Biff Microsoft Excel MVP "Mayur" wrote: 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!!! |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#VALUE!
First Question
Which one were you responding too? I want thousand seperators with no decimal places after data is entered for another month. The numbers should appears formatted (12,2343,098) Second Question This was about changing the "" to zeros which give me a new error (#DIV/0!). I want the errors to disappear and apprear properly formatted when data is enetered for each montth. I have Excel 2007. How do I add it to certain tabs. I have about forty tabs. It would take forever to do one by one. Thanks "T. Valko" wrote: 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 FormatConditional 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 -- Biff Microsoft Excel MVP "Mayur" wrote: 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!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
