The IFs have it...!!!

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I have the following formula that appears to be correct to me but Excel will not accept it...it says
it contains an error...hmmm. What this is supposed to do is: If there is a Name than depending on
the time period selected do the math or return a "N/A". If there isn't a name return a "".

What's the deal? This is far too simple a formula for me to ask for help....but I am stumped!

=IF(ISBLANK(Name_1),"",
IF(Period=1,(M12/M21),
IF(Period=3,(SUM(M12:M14)/SUM(M21:23)),
IF(Period=4,(SUM(M12:M15)/SUM(M21:24)),
IF(Period=5,(SUM(M12:M16)/SUM(M21:25)),"N/A")

Richard
 
Hi Richard!

Try:
=IF(ISBLANK(Name_1),"",
IF(Period=1,(M12/M21),
IF(Period=3,(SUM(M12:M14)/SUM(M21:M23)),
IF(Period=4,(SUM(M12:M15)/SUM(M21:M24)),
IF(Period=5,(SUM(M12:M16)/SUM(M21:M25)),"N/A")))))

Missing end parentheses plus errors in cell references.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman said:
Hi Richard!

Try:
=IF(ISBLANK(Name_1),"",
IF(Period=1,(M12/M21),
IF(Period=3,(SUM(M12:M14)/SUM(M21:M23)),
IF(Period=4,(SUM(M12:M15)/SUM(M21:M24)),
IF(Period=5,(SUM(M12:M16)/SUM(M21:M25)),"N/A")))))

Missing end parentheses plus errors in cell references.
Click to expand...
I can see the parentheses "issue", I was being lazy and letting Excel add them, sorry. But I still
can't see the problem with the cell references.

Thank you,

Richard
 
Hi Richard!

Here's one example that you had in three places:

SUM(M21:23)
instead of
SUM(M21:M23)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Jeez Norm !

I cannot believe I had to look and re-look at that cell reference
error several times before I saw it.

This must be quite a common source of error is it ?

Terr
 
Norman said:
Hi Richard!

Here's one example that you had in three places:

SUM(M21:23)
instead of
SUM(M21:M23)
Click to expand...
Thanks Norman...you don't know how many times I looked that formula other and didn't see those
errors. Common ones at that.
 
Hi Terry!

It's a common proof reading phenomenon that the closer you are to a
project the more difficult it is to spot errors.

I found it because of the highlighting feature that you get when you
are editing formulas; if the cell address is real, it is coloured, if
it is false it stays black.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
This must be a feature available in later editions of Excel.

I have 2000 and the only colours I see are the brackets.

Is there a way of making this happen in Excel 2000 as this could b
quite useful.

Thank
 
Hi Terry!

It's in Excel 2000 but in a less useful form

Put a number in A1

In A2:
=A1*5
Enter

Now use F2. You should see A1 in blue.

If I used AAZ1*5 and edit the non-cell reference is uncoloured until I
correct it.

In Excel 2003 as soon as I enter a cell reference it colours.

It looks like this enhanced feature was introduced in Excel 2002


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top