Nested functions HELP!

C

chiefnmd

=IF((C6>0)*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??
 
D

db

You need to include a second S24= preceding the 60 like so:

=IF((C6>0)*AND(S24=50,S24=60),M6/5,M6/6)
 
B

Biff

Hi!
=IF((C6>0)*AND(S24=50,60),M6/5,M6/6)
Click to expand...

In the AND function, the "60" is doing nothing! It's not being evaluated for
anything!

Try this:

=IF(AND(C6>0,S24=50),M6/5,IF(AND(C6>0,S24=60),M6/6,IF(AND(C6>0,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
 
B

Biff

=IF((C6>0)*AND(S24=50,S24=60),M6/5,M6/6)

How can S24 equal both 50 and 60 at the same time?

Biff
 
B

bj

I do not understand how your equation works but try

=m6/if(c6>0,if(or(S24=50,S24=60),5,if(S24=70,7,6)),6)
 
D

db

lol that's a good point.

chiefnmd why don't you explain, in words, what you are trying to accomplish,
that way we will better understand how you should organize your formula.
 
S

sirknightly

The Chief,

I'm not sure what you're trying to accomplish here, and this may b
usage I've never encountered before, but there are some parts of you
formula that don't make sense:

1) You're multiplying C60 by a logical statement. In essence, you'r
taking the value of C60 * either TRUE or FALSE. This is fine i
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. 6
is always 60, so there's no reason to include it in a logica
statement.

3) The result of the condition in your IF statement (based on the tw
above) is that your condition will ALWAYS give you M6/6 unless "50" i
entered in S24.

4) Because of 3) above, there is no reason to include C60 in th
formula. It serves no purpose.

From the looks of your formula, what you want it to do is multiply C6
by M6 divided by S24 divided by 10. If this is the case, use thi
formula:

=C60*(M6/(S24/10))

Let me know if I've misunderstood your intention.

Knightly
 
S

Sandy Mann

=IF(AND(C6>0,S24=50),M6/5,IF(AND(C6>0,S24=60),M6/6,IF(AND(C6>0,S24=70),M6/7,"Not
defined")
Click to expand...

Can be shortened, (but is not necessarily faster or better):


=IF(AND(C6>0,OR(S24={50,60,70})),M6/(S24/10),"Not defined")
--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
B

Biff

Hi Sandy!

If it saves key strokes, it's better! <g>

Nice one! I didn't even "see" that!

Biff
 
C

chiefnmd

=IF((C6>0)*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.
 
H

Harlan Grove

Sandy Mann wrote...
Can be shortened, (but is not necessarily faster or better):


=IF(AND(C6>0,OR(S24={50,60,70})),M6/(S24/10),"Not defined")
Click to expand...
....

Could be shortened a bit further.

=IF((C6>0)*OR(S24={50;60;70}),M6*10/S24,"Not defined")
 
C

chiefnmd

=IF((C6>0)*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((C6>0)*AND(S24=50,60),M6/5,M6/6)...
C6>0 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
 
B

Biff

See the replies by Sandy or Harlan.

Biff

chiefnmd said:
=IF((C6>0)*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((C6>0)*AND(S24=50,60),M6/5,M6/6)...
C6>0 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
Click to expand...
 
Top