| 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!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
