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?

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

"preyesone" wrote:

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?

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


"preyesone" wrote:

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

"preyesone" wrote:

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?

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

On Jun 30, 2:02*pm, preyesone
wrote:
Actually I cannot get the formula to recognize anything other than the first
condition...



"preyesone" wrote:
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(SU*M(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?- Hide quoted text -

- Show quoted text -

Hi,

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"preyesone" wrote:

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?

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.

"Shane Devenshire" wrote:

Hi,

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"preyesone" wrote:

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?

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" wrote:

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.

"Shane Devenshire" wrote:

Hi,

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"preyesone" wrote:

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?

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" wrote:

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" wrote:

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.

"Shane Devenshire" wrote:

Hi,

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"preyesone" wrote:

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?

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??

"preyesone" wrote:

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" wrote:

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" wrote:

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.

"Shane Devenshire" wrote:

Hi,

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"preyesone" wrote:

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?

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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"preyesone" wrote:

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?

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(H60.8,0.15,IF(H60.6,0.1,IF(H60 .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


On Jun 30, 1:50*pm, preyesone
wrote:
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(SU*M(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?