Home » Forums » Open Source Learning Center » Question and Answer

Using Named Ranges in If functions

Submitted by Joaye on 15 October, 2011 - 02:50

Hi all,

I tried to put in a function with named ranges but the formula did not work.. It came out as #Value!

This is the actual formula but I want to reduce it because its very long

= IF(OR(A2>=8,A3>=8,A4>=8,A5>=9,A6>=9,A7>=10)"Not Completed","Completed")

If I named range from A2 to A4 =UNI8 and
A5 to A6 = UNI9

Can I put the formula as

=IF(OR(UNI8>=8,UNI9>=9,A7>=10)"Not Completed","Completed")

Looking forward to some feedback please!

‹ Beforesave selecting all worksheets except one Lock cells in worksheet after they are changed ›
»

Any other tips to reduce the length of the formula?

Submitted by Joaye on 16 October, 2011 - 03:43.

Hi Vinesh,

Thank you for your tip! Is there any way to reduce the length of the formula? The conditions listed below are only just 1/3 of what I suppose to have.

= IF(OR(A2>=8,A3>=8,A4>=8,A5>=9,A6>=9,A7>=10)"Not Completed","Completed")

»
Vishesh's picture

If you are using Excel

Submitted by Vishesh on 15 October, 2011 - 03:23.

If you are using Excel 2007/2010 then you cannot have a named range like UNI1 or UNI9 as they are cell references in excel. And, you are trying to compare a range of cells with a single value.

»