Nested If Statements

[Sarah_Lecturer]

Hi

I am trying to create the following formulae

=IF(G7>=0,"Current",IF(G7<0,"Overdue",IF(G7=-3,"Reminder","Available")))

Unfortunately G7 contains an if statement already based on other cells so my
answer is incorrect

Can anyone give me any advice as to what to do next please? PULLING MY HAIR
OUT!!!

Thanks

Sarah

Using MS Office Excel 2003, Sp2, Windows XP - Thanks again

 

[Bernard Liengme]

It is unclear why your IF formula is noit working just because G7 contains a
formula. You tell us noithing about G7's formula. Could the problem be hat it
is returning a number which is formated? So for example iif might give -3.2
but displays -3. If this could be the problem try
=IF(G7>=0,"Current",IF(G7<0,"Overdue",IF(round(G7,0) =-3 ,"Reminder",
"Available")))
best wishes

 

[Stefi]

Syntax of this formula is correct. Your question cannot be answered without
knowing the formula in G7 and content of cells it refers to. The best you can
do is to specify your data layout and aims for us!

Regards,
Stefi

„Sarah_Lecturer†ezt írta:

 

[Sarah_Lecturer]

Hi Bernard

I think I'm making this reply complicated, but I wanted to ensure I covered
all the wacky formulas I've inherited in this spreadsheet!

The formula in G7 is:

=IF(IF($C$2-F7<0,0,$C$2-F7)=$C$2," "," "&$C$2-F7)

Cell C2 is just an auto-update of today's date and F7 is a date pulled from
another tab, the formula in there is:

=IF('1'!$G$7=0,"0",'1'!$G$7)

A little convoluted perhaps (created by someone else!) but it's designed to
compare the F7 date against the current date and give a value of the
difference, which I'm then trying to create the H 'status' column on. An
example of what I'm finally trying to achieve is:

Column: COLUMN F COLUMN G COLUMN H
Heading: Date Due Back Days Over Due Status
Data: 25/3/2009 -8
Current
20/3/2009 -3
Reminder
0
Available
15/3/2009 2
Overdue


Essentially I am trying use the IF statement in column H (Status) to show if
the date due back is current (today or future), overdue (past date), time to
send a reminder (today - 3 days) or, if it's blank, to show available.

The IF formula in column H works for overdue and reminder, but puts both
current and available as simply current.

Hopefully that's clarified what I'm trying to do... or confused everyone
even more!

Thanks again for any help!
Sarah

 

[Shane Devenshire]

Hi,

Your results are not numbers but text, that is the problem. Concatenating a
space in front of a number converts it to text. If you want to create space
us indent or the alignment commands. If you must leave the space, thus text
than change your original formula

=IF(--TRIM(G7)>=0,"Current",IF(--TRIM(G7)<0,"Overdue",IF(--TRIM(G7)=-3,"Reminder","Available")))

However, be advised that you formula is faulty. You text for <0 and then
you test for -3. If G7 is -3 it is also <0, so your last test is never
executed.

 

[Ashish Mathur]

Hi,

There is some problem in the logic. Since any number is >=0 or <0, the
result will always toggle between "Current" and "Overdue". What exactly are
you trying to do?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com