| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining two functions
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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" wrote: 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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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 - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
Awesome! Thank you so very much!
"Eduardo" wrote: 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" wrote: 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? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
Your welcome,
"preyesone" wrote: Awesome! Thank you so very much! "Eduardo" wrote: 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" wrote: 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? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
=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? |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining two functions
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combining IF OR Functions | Excel Worksheet Functions | |||
| Combining Functions | Excel Discussion (Misc queries) | |||
| Combining IF & AND functions | Excel Discussion (Misc queries) | |||
| Combining IF OR and AND functions | Excel Worksheet Functions | |||
| Combining functions | Excel Worksheet Functions | |||
Linear Mode
