Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formulas
Here is wat im trying to do. i have 3 columns (Q/R/S) That have a "Y" or "N" in them. These are to 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 to work right. Thanks -- XCESIV ------------------------------------------------------------------------ XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 View this thread: http://www.excelforum.com/showthread...hreadid=542806 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formulas
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=542806 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formulas
soz, fixed it, now it should make more sense -- XCESIV ------------------------------------------------------------------------ XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 View this thread: http://www.excelforum.com/showthread...hreadid=542806 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formulas
<fixed it
????????? -- Kind regards, Niek Otten "XCESIV" wrote in message ... | | soz, fixed it, now it should make more sense | | | -- | XCESIV | ------------------------------------------------------------------------ | XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 | View this thread: http://www.excelforum.com/showthread...hreadid=542806 | |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formulas
AS 0 is the answer to more than one set of criteria the formula can be simplified a little. I have started at the bottom of your list and 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 Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=542806 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formulas
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) "Niek Otten" wrote in message ... <fixed it ????????? -- Kind regards, Niek Otten "XCESIV" wrote in message ... | | soz, fixed it, now it should make more sense | | | -- | XCESIV | ------------------------------------------------------------------------ | XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 | View this thread: http://www.excelforum.com/showthread...hreadid=542806 | |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formulas
Thanks, Bob
Niek "Bob Phillips" wrote in message ... | 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) | | "Niek Otten" wrote in message | ... | <fixed it | | ????????? | | | -- | Kind regards, | | Niek Otten | | "XCESIV" wrote in | message | ... | | | | soz, fixed it, now it should make more sense | | | | | | -- | | XCESIV | | ------------------------------------------------------------------------ | | XCESIV's Profile: | http://www.excelforum.com/member.php...o&userid=24271 | | View this thread: | http://www.excelforum.com/showthread...hreadid=542806 | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
Complicated Formulas | Excel Worksheet Functions | |||
How prevent formulas to get external references/path to current workbook? | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |