|
Nested functions HELP!
=IF((C60)*AND(S24=50,60),M6/5,M6/6)...This function works as I desire,
however I want to add a third option where S24 would =70 and M6/7. An error occurs stating too many arguments enterd. Can some help me?? -- The Chief |
The Chief,
I'm not sure what you're trying to accomplish here, and this may be usage I've never encountered before, but there are some parts of your formula that don't make sense: 1) You're multiplying C60 by a logical statement. In essence, you're taking the value of C60 * either TRUE or FALSE. This is fine in essence, but I don't think it's doing what you intend it to. 2) The second argument in your AND statement ("60") is always TRUE. 60 is always 60, so there's no reason to include it in a logical statement. 3) The result of the condition in your IF statement (based on the two above) is that your condition will ALWAYS give you M6/6 unless "50" is entered in S24. 4) Because of 3) above, there is no reason to include C60 in the formula. It serves no purpose. From the looks of your formula, what you want it to do is multiply C60 by M6 divided by S24 divided by 10. If this is the case, use this formula: =C60*(M6/(S24/10)) Let me know if I've misunderstood your intention. Knightly Quote:
|
=IF(AND(C60,S24=50),M6/5,IF(AND(C60,S24=60),M6/6,IF(AND(C60,S24=70),M6/7,"Not
defined") Can be shortened, (but is not necessarily faster or better): =IF(AND(C60,OR(S24={50,60,70})),M6/(S24/10),"Not defined") -- Regards Sandy Replace@mailinator with @tiscali.co.uk "Biff" wrote in message ... Hi! =IF((C60)*AND(S24=50,60),M6/5,M6/6) In the AND function, the "60" is doing nothing! It's not being evaluated for anything! Try this: =IF(AND(C60,S24=50),M6/5,IF(AND(C60,S24=60),M6/6,IF(AND(C60,S24=70),M6/7,"Not defined") "Not defined" means that you haven't defined what to do if C6 is NOT greater than 0 OR S24 does NOT equal 50,60 or 70. Biff "chiefnmd" wrote in message ... =IF((C60)*AND(S24=50,60),M6/5,M6/6)...This function works as I desire, however I want to add a third option where S24 would =70 and M6/7. An error occurs stating too many arguments enterd. Can some help me?? -- The Chief |
=IF((C60)*AND(S24=50,60),M6/5,M6/6)...
In the above statement, C6 refers to a cell that if anything is there it will Reference S24 which will be placed there by another sheet, depending on if 50 hours, 60 hours or 70 hours are scheduled. At that point M6 which is a $ amount is then divided by 5 or 6 or 7. Depending on S24 being 50 or 60 or 70. The above formula works well for the first 2 but once a third is added it no longer works. -- The Chief "chiefnmd" wrote: =IF((C60)*AND(S24=50,60),M6/5,M6/6)...This function works as I desire, however I want to add a third option where S24 would =70 and M6/7. An error occurs stating too many arguments enterd. Can some help me?? -- The Chief |
Sandy Mann wrote...
=IF(AND(C60,S24=50),M6/5,IF(AND(C60,S24=60),M6/6,IF(AND(C60,S24=70),M6/7,"Not defined") Can be shortened, (but is not necessarily faster or better): =IF(AND(C60,OR(S24={50,60,70})),M6/(S24/10),"Not defined") .... Could be shortened a bit further. =IF((C60)*OR(S24={50;60;70}),M6*10/S24,"Not defined") |
=IF((C60)*AND(S24=50,60),M6/5,M6/6)...
This is how the formula should read, but let me explain what is happening. 1)On a seperate worksheet it is determined that either 50, 60, or 70 hours will be scheduled for a particular agent for a given week. 2)On 2nd worksheet C6 will be the amount of hrs that agent worked for that day. It really does not matter if it is 1 hr or more since the individual is a salary employee. Thus the need to determine how much each day's cost is for the individual, given that if he works 5 days that week (50) or 6 days(60) or 7 days(70). =IF((C60)*AND(S24=50,60),M6/5,M6/6)... C60 indicates the individual actually worked as scheduled. S24=50,60 where S24 is the hrs scheduled for the individual. M6/5 and M6/6 and M6/7 is salary divided by days scheduled thus getting a dollar value for each day that is worked, and then reported . Hope this is now clearer than mud. The Chief "sirknightly" wrote: The Chief, I'm not sure what you're trying to accomplish here, and this may be usage I've never encountered before, but there are some parts of your formula that don't make sense: 1) You're multiplying C60 by a logical statement. In essence, you're taking the value of C60 * either TRUE or FALSE. This is fine in essence, but I don't think it's doing what you intend it to. 2) The second argument in your AND statement ("60") is always TRUE. 60 is always 60, so there's no reason to include it in a logical statement. 3) The result of the condition in your IF statement (based on the two above) is that your condition will ALWAYS give you M6/6 unless "50" is entered in S24. 4) Because of 3) above, there is no reason to include C60 in the formula. It serves no purpose. From the looks of your formula, what you want it to do is multiply C60 by M6 divided by S24 divided by 10. If this is the case, use this formula: =C60*(M6/(S24/10)) Let me know if I've misunderstood your intention. Knightly chiefnmd Wrote: =IF((C60)*AND(S24=50,60),M6/5,M6/6)...This function works as I desire, however I want to add a third option where S24 would =70 and M6/7. An error occurs stating too many arguments enterd. Can some help me?? -- The Chief -- sirknightly |
See the replies by Sandy or Harlan.
Biff "chiefnmd" wrote in message ... =IF((C60)*AND(S24=50,60),M6/5,M6/6)... This is how the formula should read, but let me explain what is happening. 1)On a seperate worksheet it is determined that either 50, 60, or 70 hours will be scheduled for a particular agent for a given week. 2)On 2nd worksheet C6 will be the amount of hrs that agent worked for that day. It really does not matter if it is 1 hr or more since the individual is a salary employee. Thus the need to determine how much each day's cost is for the individual, given that if he works 5 days that week (50) or 6 days(60) or 7 days(70). =IF((C60)*AND(S24=50,60),M6/5,M6/6)... C60 indicates the individual actually worked as scheduled. S24=50,60 where S24 is the hrs scheduled for the individual. M6/5 and M6/6 and M6/7 is salary divided by days scheduled thus getting a dollar value for each day that is worked, and then reported . Hope this is now clearer than mud. The Chief "sirknightly" wrote: The Chief, I'm not sure what you're trying to accomplish here, and this may be usage I've never encountered before, but there are some parts of your formula that don't make sense: 1) You're multiplying C60 by a logical statement. In essence, you're taking the value of C60 * either TRUE or FALSE. This is fine in essence, but I don't think it's doing what you intend it to. 2) The second argument in your AND statement ("60") is always TRUE. 60 is always 60, so there's no reason to include it in a logical statement. 3) The result of the condition in your IF statement (based on the two above) is that your condition will ALWAYS give you M6/6 unless "50" is entered in S24. 4) Because of 3) above, there is no reason to include C60 in the formula. It serves no purpose. From the looks of your formula, what you want it to do is multiply C60 by M6 divided by S24 divided by 10. If this is the case, use this formula: =C60*(M6/(S24/10)) Let me know if I've misunderstood your intention. Knightly chiefnmd Wrote: =IF((C60)*AND(S24=50,60),M6/5,M6/6)...This function works as I desire, however I want to add a third option where S24 would =70 and M6/7. An error occurs stating too many arguments enterd. Can some help me?? -- The Chief -- sirknightly |
| All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com