Stdev / offset function to ignore #N/A

**joshnathan** · 01-27-2014, 08:27 AM

Hi im using the formula below but it wont work if there are any n/as unfortunately due to the way the data is presented i can't get rid of the n/a's ive normally dealt with this problem by producing a macro that removes them. But in the case of this set of data it would mean deleting data in other cols.

How can i use a formula that does the same as this but ignore n/a's? and produces the same answer.

Please Login or Register  to view this content.

it just calcs the stdev across the last 30 pieces of data. (or what ever i4 equals)

Regards,
J

**XOR LX** · 01-27-2014, 08:40 AM

Hi,

I'm struggling to understand how you could offset a cell in row 10 by 30 rows upwards? Are you sure the upper limit on I4 is not 10?

Anyway, perhaps the following array formula**:

=STDEV(IF(ISNA(N(OFFSET(AC10,-ROW(INDIRECT("1:"&$I$4))+1,,,))),"",N(OFFSET(AC10,-ROW(INDIRECT("1:"&$I$4))+1,,,))))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

**haripopuri** · 01-27-2014, 08:44 AM

Please Login or Register  to view this content.

Finish with Ctrl+Shift+Enter
I kept offset value at 10, values in K1 to K10 in my workbook.

Stdev / offset function to ignore #N/A

LinkBack

Thread Tools

Rate This Thread

Display

Stdev / offset function to ignore #N/A

Re: Stdev / offset function to ignore #N/A

Re: Stdev / offset function to ignore #N/A

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] STDEV IF function with multiple criteria

using stdev similar to the averageif function

How do I run the STDEV function with multiple criteria?

[SOLVED] Stdev function error!

STDEV function

Bookmarks

Bookmarks

Posting Permissions