Nesting IF functions within one Formula

J

Justin

I want to do the following:
IF H11>89.5% TRUE= S1
FALSE= IF H11+H12>84.5% =S2
FALSE= IF H11:H13>79.5% =S3
FALSE= IF H14>20.5% =S4

NOTE: The SUM of H11:H14 is always 100%

Here is what I've tried so far:

2 Arguments: =IF((H11)>(89%),"S1",IF(H11+H12>84%,"S2",""))

Almost 3 Arguments: =IF(H11>89%,"S1",IF(H11+H12>84%,"S2",IF
(H11:H13>79%,"S3")))

Should be 4 Arguments: =IF(H11>89.5%,"S1",IF
(H11+H12>84.5,"S2",IF(H11:H13>79.5%,"S3",IF
(H11:H13<79.4%,"S4"))))

Different Approach: =IF(H11>89.5%,"S1",IF
(H11+H12>84.5%,"S2",IF(H11:H13>79.5%,"S3",IF
(H14>20.5%,"S4"))))

I HAVEN'T HAD ANY LUCK AT ALL! I'm getting quite
frustrated because I'm sure there is a way to do this, i't
actualy a simple function. I just can't seem to find it.

Any help would be greatly appreciated, please e-mail me if
you're a genious.
 
P

Paul

Justin said:
I want to do the following:
IF H11>89.5% TRUE= S1
FALSE= IF H11+H12>84.5% =S2
FALSE= IF H11:H13>79.5% =S3
FALSE= IF H14>20.5% =S4

NOTE: The SUM of H11:H14 is always 100%

Here is what I've tried so far:

2 Arguments: =IF((H11)>(89%),"S1",IF(H11+H12>84%,"S2",""))

Almost 3 Arguments: =IF(H11>89%,"S1",IF(H11+H12>84%,"S2",IF
(H11:H13>79%,"S3")))

Should be 4 Arguments: =IF(H11>89.5%,"S1",IF
(H11+H12>84.5,"S2",IF(H11:H13>79.5%,"S3",IF
(H11:H13<79.4%,"S4"))))

Different Approach: =IF(H11>89.5%,"S1",IF
(H11+H12>84.5%,"S2",IF(H11:H13>79.5%,"S3",IF
(H14>20.5%,"S4"))))

I HAVEN'T HAD ANY LUCK AT ALL! I'm getting quite
frustrated because I'm sure there is a way to do this, i't
actualy a simple function. I just can't seem to find it.

Any help would be greatly appreciated, please e-mail me if
you're a genious.
Click to expand...

Try this:
=IF(H11>89.5%,"S1",IF((H11+H12)>84.5,"S2",IF(SUM(H11:H13)>79.5%,"S3","S4")))
)
 
P

Peo Sjoblom

You didn't mention what you want if no condition is true

=IF(H11>89.5%,"S1",IF(SUM(H11:H12)>84.5%,"S2",IF(SUM(H11:H13)>79.5%,"S3",IF(
H14>20.5%,"S4",""))))
 
D

dave!!

=IF(H11>.895,S1,IF(H11+H12>.845,S2,IF(SUM(H11:H13)>.795,S3,IF(H14>.205,S4,la
st_false_statement_here))))
 
J

Justin

One of the conditions will always be met. If I need to
put something, I guess I would just put "0".
 
J

Justin

NOPE! Didn't work.
-----Original Message-----
=IF(H11>.895,S1,IF(H11+H12>.845,S2,IF(SUM(H11:H13)
.795,S3,IF(H14>.205,S4,la
st_false_statement_here))))





.
Click to expand...
 
A

Andy B

Justin

What, exactly, is in H11 to H14? How is it formatted? Is it text or a
number?

Andy.
 
P

Paul

=IF(H11>89.5%,"S1",IF((H11+H12)>84.5%,"S2",IF(SUM(H11:H13)>79.5%,"S3","S4"))
)
works for me.
There were 2 typos in my previous post. One omitted the % sign in 84.5%. The
other was an extra right bracket at the end.
You don't need a fourth IF because (as you have said in other replies) one
of your four conditions will always be met.
 
J

Justin

This is my first time using this board..... I don't see
any attachments? Do they get erased after a certain
period of time? Could you cut and paste the Formula to
the board for me or e-mail me the file? Hope you can help,
Thanx.
 
C

corinereyes

Hi,

I think you should re-analyze on what your trying to show results based
from your formula. Your formula is like this from your own example:

I want to do the following:
IF H11>89.5% TRUE= S1
FALSE= IF H11+H12>84.5% =S2
FALSE= IF H11:H13>79.5% =S3
FALSE= IF H14>20.5% =S4
NOTE: The SUM of H11:H14 is always 100%
Click to expand...

We can make a formula based from your example but we can't have correct
results. Why? Because the conditions from H11 to H13 is the same
condition with H11>89.5%. They have the same results. Example: if you
have 90% in H11, 2% in H12 and 1% in H13, if you will sum that the
answer is 93%, 93% is greater than 84.5% and 79.5%, so your condition
can't exactly show the results you want, results can be either S2 or
S3. So pls. re-analyze your problem. But I have think of a way to show
S1 or S2 or S3 and if none of the condition the formula will display
"-"(charcater). Try having another column and sum H11:H12 and H11:H13
(column I), then try this formula

=IF(H11>=89.5%,"S1",IF(I12>84.5%,"S2",IF(I13>=79.5%,"S3",IF(H14>20.5%,"S4","-"))))


Regards,

Corine

if you any question regarding my comment pls email me at
(e-mail address removed)
 
Top