(Excel) Nested IF or AND Statement

Question

I have an Excel spreadsheet that averages a set of various data. I need a way to display nothing if cell N2 contains the value 9999.09, to make sure that value is not included in the averaging.

Excel code that needs to be modified:

=IF(T2+0<5,N6,N4)

I tried

=IF(AND(T2+0<5,N2=9999.09,N6,N4))

but that doesn't work because it really is a two condition statement. Also cell T2 displays the current hour.

Not really clear on the problem you are describing. But can you not just use IF(N2<>9999.09,N2,"") ? The fact that you are giving such a specific target figure (9999.09) makes me think I've misunderstood. I also don't understand where the values have come from in T2+0<5 — roganjosh
– roganjosh, Commented Aug 11, 2015 at 10:40
Hi roganjosh, "T2+0>5" refers to the current hour and 5 is a specific hour in time, e.x. 5:00am. — Aaron Perry
– Aaron Perry, Commented Aug 11, 2015 at 10:43
Just FYI, you never closed the "AND" for the =if(and(.... Did you perhaps mean =IF(AND(T2+0<5,N2=9999.09),N6,N4)? — BruceWayne
– BruceWayne, Commented Aug 11, 2015 at 15:33

JamTay317 · Accepted Answer · 2015-08-11 10:43:02Z

1

=IF(N2=9999.09,"", Do what you wish)

you can pass through "" as a null text string.

answered Aug 11, 2015 at 10:43

JamTay317

1,0173 gold badges20 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aaron Perry Over a year ago

This appears to accomplish what I want. =IF(N2=9999.09,"",IF(T2+0<5,N4,N2))

AlexT82 Over a year ago

better version would be to use an array formula: =AVERAGE(IF(RANGE=TESTVALUE,RANGE)) and close of with Control+Shift+Enter

roganjosh Over a year ago

@user2697139 I'm not sure how that's a "better" answer as it has none of the additional functionality eluded to in the question or in the OP's comment.

Collectives™ on Stack Overflow

(Excel) Nested IF or AND Statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related