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
