Nested Functions

[Bar]

Brain has gone completely blank this morning.

I am trying to create a nested function but keep getting a #VALUE! error and I cannot work out what I have done wrong.

the formula so far looks like this

=IF(SUM(G3*H3)>9,"High",AND(IF(SUM(H3*G3)>5<9,"Medium"),AND(IF(SUM(H3*G3)<5,"Low"))))

G3 and H3 return a value looked up from an array - this part works.

Unfortunately when I try the above formula it works if the value is greater than 9 but I cannot get it to work with over values.

What obvious thing am I doing wrong?

 

[Inquisitor]

You should get a better response here:
http://forums.overclockers.co.uk/forumdisplay.php?f=41

Don't bother reposting though, I'm sure a passing Don will move it for you

 

[Belmit]

Shouldn't it be 'OR', not 'AND'? Been a while since I did this sort of thing...

 

[Beansprout]

=IF(
     SUM(G3*H3)>9,
     "High",
     AND(
           IF(
              SUM(H3*G3)>5<9,
              "Medium"
              ),
      AND(
            IF(
               SUM(H3*G3)<5,
               "Low"
               )
            )
   )

Edit: That's just to lay it out more easily, I've no idea about Excel

 

[Beansprout]

oo double post

 

[bam0]

You don't nest with multiple parentheses, try this;

=IF(SUM(G3*H3)>9,"High",IF(AND((SUM(H3*G3)>5),(SUM(H3*G3)<9)),"Medium",IF(SUM(H3*G3)<5,"Low")))

edit: forgot about your middle condition.

 

[Bar]

Spot on Bam0 - knew it would be a stupid mistake.

Now for some reason it won't accept the >5<9 parameter.

Any thoughts?

 

[Stringy]

Spot on Bam0 - knew it would be a stupid mistake.

Now for some reason it won't accept the >5<9 parameter.

Any thoughts?

>5 AND <9? (can't remember if that's what you actually put in excel, been a while since I used it)

 

[Bar]

ignore my last post - being a muppet here :/

 

[DannyDan]

I think it should be something like this...

=IF(SUM(G3*H3)<9,IF(SUM(G3*H3)>5,"Medium","Low"),"High")

"Low" is displayed if it is less than 9 and less than 5, "Medium" if it is less than 9 but greater than 5, and "High" if it is greater than 9.

Havent got Excel on this PC so I might have missed something out or made a mess of the brackets