# 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:

=CEILING(A1*0.995,0.05)

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:

=IF(ERROR.TYPE(CEILING(A5*0.995,0.05))=3,"junk")

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):

=IF(ERROR.TYPE(CEILING(D4*0.995,0.05))=3,"junk",CEILING(D4*0.995,0.05))

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

Help!

Nick

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

=IF(ISERR(CEILING(A1*0.995,0.05)),0,CEILING(A1*0.995,0.05))

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!

Nick