Nested If Statement, Ranges?

Microsoft Office excel 2007
November 24, 2010 at 12:24:44
Specs: Windows XP
I'm making a spreadsheet to track whether course prerequisites are being met by the recommended course sequence at the university where I work. What I am wondering is whether students can meet all their prerequisites if they take their courseload in the order we recommend.

On my sheet, course numbers are listed in the order they must be taken down column C. Prerequisites for each of these courses are in column H. My nested IF statement currently looks like this:

=IF(OR($C$5 = H18, $C$8 = H18, $C$9=H18, $C$10=H18, $C$11=H18,$C$12=H18,$C$13=H18,$C$14=H18,$C$15=H18), "yes", "NO")

The idea is that the function will check each of the C cells for the prerequisite value in H. If the prereq. has been met, a "yes" will appear. If not, then a "no," and I will know to adjust the recommended course sequence accordingly.

The way it is now technically works. But is is possible to replace all the individual C cells I'm listing with a range, something like $C$5:$C$15? Because I have to maintain this sheet, it would be much easier to update it each semester if I could use ranges instead of having to type out each cell I'd like checked.

Is this possible??

See More: Nested If Statement, Ranges?

Report •

November 24, 2010 at 17:05:26
You could try a =COUNTIF() function:


If the string H18 appears within the rage c5:c15 the =COUNTIF() will return the the number of times it does.

if H18 appears once, you get 1
if H18 appears twice, you get 2
if H18 does not appear, you get 0


Report •

November 24, 2010 at 18:02:11
If you want to stick with your "YES" or "NO" then this should work:


Also the string "H18" can be replaced by a cell reference.
If the string H18 is entered into cell D1 then


will also work.

This allows you to make a list and just reference the appropriate cell in the list, as opposed to entering the string by hand all the time.


Report •

November 30, 2010 at 12:08:16
That is fantastically easier, and so helpful! Thank you!

Report •

Related Solutions

November 30, 2010 at 13:12:07
Glad I could help.


Report •

Ask Question