Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have several sheets wherein I need to ensure and validate that all the 'numbers' (values) within predetermined ranges are actually numbers. We can assume that all numeric values are valid. The ranges however are not exactly predetermined, since the are re-populated daily with new data imports so that will mean more rows - however the starting cell reference, O3 is expected to always be the same.

Currently I am using COUNT(O3:BB1740) and COUNTA(O3:BB1740) and then finding the difference between the two values (if the difference is not zero it indicates that there is data within the range that is not numeric).

This method seems hackish to me so I am looking for a reasonable alternative.

  • If possible, how would I isolate and identify the invalid value (I know I can loop though each cell but I am looking for a non-macro approach if possible)

(It would be awesome if there was a IsNumber function for ranges)

share|improve this question

1 Answer 1

up vote 3 down vote accepted

A more elegant way is to use an array formula. This will return TRUE if every number in range is a number or FALSE if range contains non-numbers or blanks.

{=AND(ISNUMBER(range))}

To isolate the non-number values in the range you can apply Conditional Formatting using a similar formula.

Hint: To create an array formula, press Ctrl + Shift+ Enter instead of Enter when entering/editing the cell.

share|improve this answer
    
Is there any way to exclude blanks? –  Ahmad Nov 18 '10 at 10:35
    
one issue, excel constantly complains about running out of memory when using this function. i do however have several sheets which uses this function... –  Ahmad Nov 18 '10 at 13:45
    
@Ahmad: Not as simply. You could use a function that requires all numbers or blanks as input, such as GCD() in a formula like =ISNUMBER(GCD(range)) (not an array formula). It is shorter than your orignal idea but still 'hackish' :) –  Mike Fitzpatrick Nov 18 '10 at 13:56
    
@Ahmad: I didn't notice your range was so large. I am surprised my first solution gives you memory errors but, given that it does, I wouldn't recommend my second GCD solution either. If your own solution is working then you should probably stick with it. –  Mike Fitzpatrick Nov 18 '10 at 14:06
    
I'm not sure, but i guess that array formulas are volatile. What i did though was to run the formula from a macro for each sheet, but immediately remove it after I got the result. –  Ahmad Nov 19 '10 at 8:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.