Combining two functions

[preyesone]

I am trying to combine the IF/And functions:
=IF(SUM(E4+F4+(G4/H4)*0.8)>0.5,0.05,IF(SUM(E4+F4+(G4/H4)*0.8)>0.6,0.1,IF(SUM(E4+F4+(G4/H4)*0.8)>0.8,0.15,AND(J4="Yes",1))))

However it is not recognizing the And portion. No matter what the formula
total is, it should give a 1 if cell J is yes. Can someone help me?

 

[preyesone]

Actually I cannot get the formula to recognize anything other than the first
condition...

 

[Shane Devenshire]

Hi,

The AND portion says if J4="Yes" and 1 then TRUE, otherwise FALSE. why not
just say IF(J4="yes",1,"whatever")

 

[preyesone]

Unfortunately it's only picking up the first portion of the formula and
giving a .05 for the answer no matter what the SUM is.

 

[Eduardo]

Hi,
try

=IF(AND(SUM(E4+F4+(G4/H4)*0.8)<0.6,J4="Yes"),0.05,IF(AND(SUM(E4+F4+(G4/H4)*0.8)<0.8,J4="Yes"),0.1,IF(AND(SUM(E4+F4+(G4/H4)*0.8)>0.8,J4="Yes"),0.15)))

 

[preyesone]

Nope! Now I have to have J4 as Yes to get any answers. IF J4 is populated
with Yes, then the total should be 1
For > .5 = 0.5, >.7 = .1, > .8=.15, >.99=1

 

[Eduardo]

Hi,
Sorry I don't understand something, if J4 has yes you want 1 otherwise you
want 0.5 or 0.8 etd, is that correct??

 

[Ken]

I think you have two major issues and some minor complications. If
you want to to give 1 if H4 us "yes" regardless of the other stuff,
then you probably want to use OR instead of AND. The second issue is
that the AND (which should probably be OR) is only a factor when e4+f4+
(G4/H4)*.8<.5. If that is the case it will also be less than .6 and
less than .8; hence you should never see a .1 or a .15. If your
numeric expression is less than .5 then the function will return your
last argument which is AND(j4="yes",1) which is is True if j4 is "yes"
and False otherwise.

I can't tell for sure, but, it appears that you need something along
the lines of:

=if(j4<>"Yes",IF(e4+f4+(g4/h4)*.8>.8,.15,IF(e4+f4+(g4/h4)*.8>.6,.1,IF
(e4+f4+(g4/h4)*.8>.5,.05,1))))

Instead of AND or OR, I added another IF; which I think does what you
want.

This gets a lot easier to follow if you make cell H6 equal to the
expression e4+f4+(g4/h4)*.8. Then it simplifies to:

=IF(J4<>"Yes",IF(H6>0.8,0.15,IF(H6>0.6,0.1,IF(H6>0.5,0.05,1))))

which is what I think you were trying to do. It seems the SUMs were
also unnecessary.

Good luck.

Ken
Norfolk, Va

 

[Eduardo]

I hit enter before finishing, is that is the case

=IF(J4="Yes",1,IF(SUM(E4+F4+(G4/H4)*0.8)<0.6,0.05,IF(SUM(E4+F4+(G4/H4)*0.8)<0.8,0.1,IF(SUM(E4+F4+(G4/H4)*0.8)>0.8,0.15))))

 

[Ken]

That is because if it meets the first condition the second condition
never even gets checked; you need to reverse your conditions.
Ken

 

[preyesone]

Awesome! Thank you so very much!

I hit enter before finishing, is that is the case

=IF(J4="Yes",1,IF(SUM(E4+F4+(G4/H4)*0.8)<0.6,0.05,IF(SUM(E4+F4+(G4/H4)*0.8)<0.8,0.1,IF(SUM(E4+F4+(G4/H4)*0.8)>0.8,0.15))))

 

[Eduardo]

Your welcome,

Awesome! Thank you so very much!

 

[Teethless mama]

=MAX(LOOKUP(E4+F4+(G4/H4)*0.8,{-1E+100,0.6,0.8},{0.05,0.1,0.15}),(J4="Yes"))

 

[Shane Devenshire]

Hi,

Careful with this formula!

A few ideas and issues with your formula:

=IF(J4="Yes",1,IF(SUM(E4+F4+(G4/H4)*0.8)<0.6,0.05,IF(SUM(E4+F4+(G4/H4)*0.8)<0.8,0.1,IF(SUM(E4+F4+(G4/H4)*0.8)>0.8,0.15))))

this returns FALSE if the sum is 0.8!

Here is are a number of other solutions which are shorter and handle .8:

=IF(J5="Yes",1,IF((E5+F5+G5/H5*0.8)<0.6,0.05,IF((E5+F5+G5/H5*0.8)<0.8,0.1,IF((E5+F5+G5/H5*0.8)>=0.8,0.15))))

=IF(J5="Yes",1,IF((E5+F5+G5/H5*0.8)<0.6,0.05,IF((E5+F5+G5/H5*0.8)<0.8,0.1,0.15)))

you may need to modify this to <=0.8 depending on how you are trying to
handle this issue.

Better might be to create a lookup table

M N
0 0.05
0.599 0.1
0.799 0.15

and use

=IF(J4="Yes",1,VLOOKUP(E4+F4+G4/H4*0.8,M1:N3,2))