Anyone good with nested formulas?

Lil306

Just did my IT exam today for Excel, had to do a nested formula which was not in the workbook so needed some help. Anyone able to understand them, can you help explain the setup of them for me ?

Example:
IF Cell B2, was below 2 it would give the word "Bob", and if it was above 30 it would give say "Tom". Here's the formula (I know it's wrong, but I'm close somewhere), could someone clarify it. I won't try and break it down or explain it because those who understand won't need it. Though I'd appreciate if you could break down the command for me and highlight where I've gone wrong

=IF(B2=0,"","(IF(B2<2,"Bob",(IF(B2>30,"Tom",""")))))

fitshase

=IF(B2=0,"",(IF(B2<2,"Bob",(IF(B2>30,"Tom","")))))

will give the following:-
B2 = 0 will show " " (without the "" - i.e., blank)
B2 = 1 will show "Bob"
B2 = 2 up to 30 will show " " (without the "" - i.e., blank)
B2 = over 30 will show "Tom"

Lil306

fitshase wrote: »

=IF(B2=0,"",(IF(B2<2,"Bob",(IF(B2>30,"Tom","")))))

will give the following:-
B2 = 0 will show " " (without the "" - i.e., blank)
B2 = 1 will show "Bob"
B2 = 2 up to 30 will show " " (without the "" - i.e., blank)
B2 = over 30 will show "Tom"

Thanks, it's normally the quotations where I go a bit funny

I understand the logic behind them (this value is true, give this answer etc) just it's a bit hard to get your head round

fitshase

your mistake was putting an extra " after the second ,

also extra " at the end needed removing

However - if you put a figure in which is between 2 and 30, the cell won't show anything.

Lokolo

If its below 2, its Bob, if its above 30 its Tom. Anything inbetween is blank.

If B2 < 2
->DISPLAY BOB
else
->If B2 > 30
->->DISPLAY TOM
->End if
End if

So overall answer:

If(B2<2,"Bob",(If(B2>30,"Tom","")))

---

I am not sure why B2 = 0 then blank, unless that was part of the question (in which case you have left it out)

Billy-no-Money

You don't need quite so many brackets:
=IF(B2=0,"",IF(B2<2,"Bob",IF(B2>30,"Tom","")))

(EDIT: Obviously depends what you're trying to do!)

Lil306

Thank you all,

I'll explain it better, the spreadsheet was to be designed, so that the adjacent cell displayed one of two values depending on criteria, otherwise it displayed nothing and just left the value entered inside that cell (see attached)

EDIT: Here's a quick example
http://spreadsheets.google.com/ccc?key=0AtUiA88EEQ-UdDFVdVNTZkctTHo4THhIbEZFaG9HdFE&hl=en_GB

If you enter 1, or 31 you will notice it adds in the required values from the IF statement in column E. Otherwise it adds nothing in column E when the values are between 2 and 30

This is how it was setup.... I think this may clarify it a bit easier

Lokolo

Did you want data validation to?

Currently in your formula you can enter negative numbers and it would return "Day Tripper".

If you want validation too:

IF(B2<1,"ERROR",(IF(B2<3,"Day Tripper",IF(B2>30,"Too Long","Anything between 2 and 30 days"))))

Enter that into excel

Lil306

Lokolo wrote: »

Did you want data validation to?

Currently in your formula you can enter negative numbers and it would return "Day Tripper".

If you want validation too:

IF(B2<1,"ERROR",(IF(B2<3,"Day Tripper",IF(B2>30,"Too Long","Anything between 2 and 30 days"))))

Enter that into excel

It didn't specifcally want data validation, it only had to display those 2 values when those criteria were met (in attached file)

Lokolo

Lil306 wrote: »

It didn't specifcally want data validation, it only had to display those 2 values when those criteria were met (in attached file)

OK well in that case you only need 2 IF statements.

IF(B2<2,"Day Tripper",(IF(B2>30,"Too Long")))

You having the B2=0 means the equation is technically wrong. (as the question said anything below should be Day Tripper, not blank)

EDIT: You attachment isn't available.

[SIZE=+1]We're sorry, [EMAIL="xxx&#64;gmail.com"]myaddress@gmail.com[/EMAIL] does not have permission to access this spreadsheet.[/SIZE]

Lil306

Thanks for help

I think in the actual exam, it wasn't required nor was it stated it was so it was a bit of a grey area. In the help I was given however it stated the correct answer should have been the "", so I'm assuming column E was to be setup like following

If the value entered in Column D, was <2 - it would produce "Day-Tripper" in E
If the value entered in Column D, was >30 - it would produce "Too Long" in E
Otherwise, leave the column E blank

I think I know what the question was supposed to mean...

Blame the exams but at least I understand the functions a whole lot better now

Would the query =IF(B2=0,"","hellno") produce the following query?
- If cell B2, has a no value present, value if True (leave blank cell), False (hellno)?
(I'm assuming 0 means no data as opposed to the number 0)