Complicated formulas

X

XCESIV

Here is wat im trying to do.
i have 3 columns (Q/R/S) That have a "Y" or "N" in them. These are t
be the variables to decide the formula.

P=157.67

If only Q102="Y" then cell M102 = P102 cells M103 and M104=0
If only Q102="Y" then cell M103 = P102 cells M102 and M104=0
If only Q102="Y" then cell M104 = P102 cells M102 and M103=0
If Q102 and R102 = "Y" then cell M102 = (P102*ALA_CF)+((P102*ALA_SU)/2
and cell M103 = (P102*ALA_RF+((P102*ALA_SU)/2) and cell M104=0
If Q102 and S102 = "Y" then cell M102 = (P102*ALA_CF)+((P102*ALA_RF)/2
and cell M103=0 and cell M104 = (P102*ALA_RF+((P102*ALA_RF)/2)
If R102 and S102 = "Y" then cell M102=0 and cell M103
(P102*ALA_RF)+((P102*ALA_CF)/2) and cell M104
(P102*ALA_SU+((P102*ALA_CF)/2)
if all 3 columns = "y" then M102 = (P102*ALA_CF and cell M103
(P102*ALA_RF) and cell M103 = (P102*ALA_RF)

i know it has to be setup right in the 3 lines but I couldnt get it t
work right.


Thank
 
D

Dav

Your explaination does not make sense
for example the first 3 lines
If only Q102="Y" then cell M102 = P102 cells M103 and M104=0
If only Q102="Y" then cell M103 = P102 cells M102 and M104=0
If only Q102="Y" then cell M104 = P102 cells M102 and M103=0

If Q102=y m102=p102 in the second line M102 now equals 0 for the same
condition, which is also the case in the third line

Unless you state it clearly you will not get an answer, as it is
impossible to answer

Regards

Dav
 
D

Dav

AS 0 is the answer to more than one set of criteria the formula can b
simplified a little. I have started at the bottom of your list an
worked up, so the last condition is 0

You probably will want to check these but try

In M102
=if(and(q102=”Y”,R102=”Y”,S102=”Y”)
P102*ALA_CF,if(and(q102=”Y”,R102=”Y”), (P102*ALA_CF)+((P102*ALA_SU)/2)
if(and(q102=”Y”,s102=”Y”), (P102*ALA_CF)+((P102*ALA_RF)/2)
if(q102=”Y”,P102,0))))

In M103
=if(and(q102=”Y”,R102=”Y”,S102=”Y”)
P102*ALA_RF,if(and(R102=”Y”,S102=”Y”), (P102*ALA_RF)+((P102*ALA_CF)/2)
if(and(q102=”Y”,r102=”Y”), (P102*ALA_RF+((P102*ALA_SU)/2)
if(R102=”Y”,P102,0))))

In M104
=if(and(q102=”Y”,R102=”Y”,S102=”Y”)
P102*ALA_SU,if(and(R102=”Y”,S102=”Y”), (P102*ALA_SU+((P102*ALA_CF)/2)
if(and(q102=”Y”,s102=”Y”), (P102*ALA_RF+((P102*ALA_RF)/2)
if(s102=”Y”,P102,0))))

regards

Da
 
B

Bob Phillips

He means he has edited the post in excelforum to make it clearer Niek.
Doesn't realise that we don't see that update.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
N

Niek Otten

Thanks, Bob

Niek
| He means he has edited the post in excelforum to make it clearer Niek.
| Doesn't realise that we don't see that update.
|
| --
| HTH
|
| Bob Phillips
|
| (remove xxx from email address if mailing direct)
|
| | > <fixed it>
| >
| > ?????????
| >
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| >
| message
| > | > |
| > | soz, fixed it, now it should make more sense
| > |
| > |
| > | --
| > | XCESIV
| > | ------------------------------------------------------------------------
| > | XCESIV's Profile:
| http://www.excelforum.com/member.php?action=getinfo&userid=24271
| > | View this thread:
| http://www.excelforum.com/showthread.php?threadid=542806
| > |
| >
| >
|
|
 
Top