Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to correct formula error?
http://tinyurl.com/47h8lb
Using Excel 2003 in WIN XP Link above has a pic of part of the spreadsheet showing errors Simple formula adding cells returns a #value! error. If I just enter 0.00 in each cell referenced in the formula, the error is solved (as in row 22. How can I set the options so that if a cell is just missing amounts, the formula will still work correctly? I could fill in 0.00 in empty cells, but there are roughly 1500 rows of data............ TIA Doug |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to correct formula error?
You don't show us what is in H24. Anyway, if any of those cells have text in
them (including a blank space, which wouldn't be visible), then that would generate the error you are getting; so that is the first thing you should check Rick "Doug Boufford" wrote in message ... http://tinyurl.com/47h8lb Using Excel 2003 in WIN XP Link above has a pic of part of the spreadsheet showing errors Simple formula adding cells returns a #value! error. If I just enter 0.00 in each cell referenced in the formula, the error is solved (as in row 22. How can I set the options so that if a cell is just missing amounts, the formula will still work correctly? I could fill in 0.00 in empty cells, but there are roughly 1500 rows of data............ TIA Doug |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to correct formula error?
Rick
H24 is empty. I have gone thorough & formatted all cells involved in the formulae as numbers. None of these cells have text typed into them. How can I check to see if a text blank space is entered - without looking at 5 columns x 1500 rows.? Thanks Rick Rothstein (MVP - VB) wrote: You don't show us what is in H24. Anyway, if any of those cells have text in them (including a blank space, which wouldn't be visible), then that would generate the error you are getting; so that is the first thing you should check Rick "Doug Boufford" wrote in message ... http://tinyurl.com/47h8lb Using Excel 2003 in WIN XP Link above has a pic of part of the spreadsheet showing errors Simple formula adding cells returns a #value! error. If I just enter 0.00 in each cell referenced in the formula, the error is solved (as in row 22. How can I set the options so that if a cell is just missing amounts, the formula will still work correctly? I could fill in 0.00 in empty cells, but there are roughly 1500 rows of data............ TIA Doug |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to correct formula error?
Before tackling all 1500 rows of data, I figured we could try and find out
what was wrong on Row 24 (the one you highlighted in your sample)... the odds are whatever is producing the error there probably is at the root of the problem elsewhere. Did you click in each of the H24, K24, L24, M24 and N24 to see if there were blanks in them? Also, I guess I should ask to be sure, are there any formulas in those cells or are they pure user-entered data? Rick "Doug Boufford" wrote in message ... Rick H24 is empty. I have gone thorough & formatted all cells involved in the formulae as numbers. None of these cells have text typed into them. How can I check to see if a text blank space is entered - without looking at 5 columns x 1500 rows.? Thanks Rick Rothstein (MVP - VB) wrote: You don't show us what is in H24. Anyway, if any of those cells have text in them (including a blank space, which wouldn't be visible), then that would generate the error you are getting; so that is the first thing you should check Rick "Doug Boufford" wrote in message ... http://tinyurl.com/47h8lb Using Excel 2003 in WIN XP Link above has a pic of part of the spreadsheet showing errors Simple formula adding cells returns a #value! error. If I just enter 0.00 in each cell referenced in the formula, the error is solved (as in row 22. How can I set the options so that if a cell is just missing amounts, the formula will still work correctly? I could fill in 0.00 in empty cells, but there are roughly 1500 rows of data............ TIA Doug |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to correct formula error?
Clicked in each cell & saw nothing. If I entered 0.00 in each "empty"
cell, then formula was valid. (This is a spreadsheet I made up for a client who said that they "Excel experts" and who were supposed to be entering data) Found the trick of entering a 1.00 in blank cell & doing Edit copy, then Paste Special - Multiply to rest of Range. Still leaves some lines where formula is #Valid!, but "cures" most of the probs. Doug Rick Rothstein (MVP - VB) wrote: Before tackling all 1500 rows of data, I figured we could try and find out what was wrong on Row 24 (the one you highlighted in your sample)... the odds are whatever is producing the error there probably is at the root of the problem elsewhere. Did you click in each of the H24, K24, L24, M24 and N24 to see if there were blanks in them? Also, I guess I should ask to be sure, are there any formulas in those cells or are they pure user-entered data? Rick "Doug Boufford" wrote in message ... Rick H24 is empty. I have gone thorough & formatted all cells involved in the formulae as numbers. None of these cells have text typed into them. How can I check to see if a text blank space is entered - without looking at 5 columns x 1500 rows.? Thanks Rick Rothstein (MVP - VB) wrote: You don't show us what is in H24. Anyway, if any of those cells have text in them (including a blank space, which wouldn't be visible), then that would generate the error you are getting; so that is the first thing you should check Rick "Doug Boufford" wrote in message ... http://tinyurl.com/47h8lb Using Excel 2003 in WIN XP Link above has a pic of part of the spreadsheet showing errors Simple formula adding cells returns a #value! error. If I just enter 0.00 in each cell referenced in the formula, the error is solved (as in row 22. How can I set the options so that if a cell is just missing amounts, the formula will still work correctly? I could fill in 0.00 in empty cells, but there are roughly 1500 rows of data............ TIA Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i correct this error | Excel Discussion (Misc queries) | |||
how to correct a #value error | Excel Discussion (Misc queries) | |||
Date Format Correct, But Formula Error | Excel Discussion (Misc queries) | |||
How do I correct error using the =IF(AND(formula in excel? | Excel Worksheet Functions | |||
How do I correct error using the =IF(AND(formula in excel? | Excel Worksheet Functions |