MrExcel Publishing
Your One Stop for Excel Tips & Solutions

using IF and Error.type with a formula

Posted by Nickt on October 24, 2000 9:19 AM

I have a spreadsheet of prices. For the sake of discussion assume all the prices are in column A. In column B I want to calculate a 1/2%discount and round to the nearest $0.05. This I've accomplished with this formula:


The problem is this spreadsheet has thousands of prices on it and there are multiple blank cells in column A. This results in #VALUE! being displayed in colum B. Using Excel help I know I can replace the #VALUE! with a bit of text using the formula:


The problem is getting these two formulas to work together. From reading the Excel help I cam up with this formula (and a few variations):


unfortunately it will not work. :( It returns #NA for all cells which should contain a number, and junk for those that shouldn't.



Posted by Nickt on October 24, 2000 11:01 AM

**One minor correction. the formula that I've tried is:

Posted by Neil on October 24, 2000 12:12 PM

Try this


You can replace the ,0, with whatever you like - "junk" in your example

Posted by Nickt on October 24, 2000 12:26 PM

Hey, it works! Thank you very very much!