Nested IF problems

[Emo Elmo]

Hello,

I am trying to figure out how to use nested if's but am unable to get it to work


this is the formula i am using
=IF(D4<B18,A19,IF(D4>B18<B17,A18,IF(D4>B17,A17)))

here is a screen shot of how my spreadsheet is setup
\1

What am i doing wrong?



any help much appreciated
Thanks
EE

[martindwilson]

=if(d4<b18,a19,if(d4<b17,a18,a17))
no need for between conditions as each test is done in order so if its less than 50 fail if it passes that test i.e 50+ then the next test is applied "is it less than 80 " if its not then give end result
what you were trying to write is this
=IF(D4<b18,A19,IF(AND(D4>=b18,D4<b17),A18,A17))
which is as mentioned above unecessary but it will still work

[zbor]

try this:

=IF(D4<$B$18;$A$19;IF(D4<$B$17;A18;A17))

Edit: replace ; with ,
Edit2: martindwilson well noticed mistake

[martindwilson]

zebor your point is? not only is that just reiterating what ive posted but it is also wrong!
=IF(D4<B4

[zbor]

Yes, you a right, didn't watch carefully.
Also, when I posted this I didn't see you already post solution. Window was oppened, I tested formula and copy-paste it. If it's problem I'll remove it.

[DonkeyOte]

Another alternative to using an IF I've interpreted correctly:

First enter 0 in to B19

Then:

D4: =INDEX($A$17:$A$19,4-COUNTIF($B$17:$B$19,"<="&B4))
can be applied to E4:G4 and all rows below

If you altered the ordering of your marks (from low to high) you could use a simpler method still, ie:

A17: Fail: B17: 0
A18: Pass Mark: B18: 50
A19: Merit Mark: B19: 80

D4: =LOOKUP(B4,$B$17:$B$19,$A$17:$A$19)
can be applied across all result cells

[martindwilson]

zebor only joking with you.lol but it was a glaring typo!!!! i know what its like, someone asks a pivot table question ,you go away build the table find the answer post it in the still open window, only to find 10 others have already given the solution!

[Emo Elmo]

thanks all

Martindwilson, your solution works perfectly after a little alteration being adding $$ to some of the cell references eg. $B$17 so it doesent change that cell as it goes down the marks

[zbor]

zebor only joking with you.lol but it was a glaring typo!!!! i know what its like, someone asks a pivot table question ,you go away build the table find the answer post it in the still open window, only to find 10 others have already given the solution!

Is that zebor thing also some joke I don't understand

[martindwilson]

Touché !My apologies Zbor