Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With If Formula
I have the following monitoring tool:
Engine Trading 1 TRUE 1 TRUE 1 TRUE 2 TRUE 2 FALSE 2 TRUE I am looking for a formula to place in ColC of this table that will look at the monitoring tool above and if all values for a given engine are "true", return the value that is in ColB. I've filled in the table below with the result I am trying to achieve. Engine String Status 1 ABC ABC 2 EFG Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With If Formula
I'll assume that the monitoring data is on Sheet1 and the table you're trying
to compile on Sheet2 with column headers in row 1. In cell C2 on Sheet2, enter the formula =if(sumproduct(--(Sheet1!A$1:A$10000=a2),--(Sheet1!B$1:B$10000="FALSE"))=0,b2,"") This counts the number of times FALSE appears with the particular engine, and sets copies the value from b2 to c2 if the count is 0. HTH. --Bruce "carl" wrote: I have the following monitoring tool: Engine Trading 1 TRUE 1 TRUE 1 TRUE 2 TRUE 2 FALSE 2 TRUE I am looking for a formula to place in ColC of this table that will look at the monitoring tool above and if all values for a given engine are "true", return the value that is in ColB. I've filled in the table below with the result I am trying to achieve. Engine String Status 1 ABC ABC 2 EFG Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With If Formula
Thank you Brian. I tried this formula but it returns the value in b2 even if
some values in Sheet1!B$1:B$10000="FALSE" are indeed false. Am I doing something wrong ? "bpeltzer" wrote: I'll assume that the monitoring data is on Sheet1 and the table you're trying to compile on Sheet2 with column headers in row 1. In cell C2 on Sheet2, enter the formula =if(sumproduct(--(Sheet1!A$1:A$10000=a2),--(Sheet1!B$1:B$10000="FALSE"))=0,b2,"") This counts the number of times FALSE appears with the particular engine, and sets copies the value from b2 to c2 if the count is 0. HTH. --Bruce "carl" wrote: I have the following monitoring tool: Engine Trading 1 TRUE 1 TRUE 1 TRUE 2 TRUE 2 FALSE 2 TRUE I am looking for a formula to place in ColC of this table that will look at the monitoring tool above and if all values for a given engine are "true", return the value that is in ColB. I've filled in the table below with the result I am trying to achieve. Engine String Status 1 ABC ABC 2 EFG Thank you in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With If Formula
Are the false values text or are they the results of logical comparisons? If
the latter, remove the quotes around the FALSE. --Bruce "carl" wrote: Thank you Brian. I tried this formula but it returns the value in b2 even if some values in Sheet1!B$1:B$10000="FALSE" are indeed false. Am I doing something wrong ? "bpeltzer" wrote: I'll assume that the monitoring data is on Sheet1 and the table you're trying to compile on Sheet2 with column headers in row 1. In cell C2 on Sheet2, enter the formula =if(sumproduct(--(Sheet1!A$1:A$10000=a2),--(Sheet1!B$1:B$10000="FALSE"))=0,b2,"") This counts the number of times FALSE appears with the particular engine, and sets copies the value from b2 to c2 if the count is 0. HTH. --Bruce "carl" wrote: I have the following monitoring tool: Engine Trading 1 TRUE 1 TRUE 1 TRUE 2 TRUE 2 FALSE 2 TRUE I am looking for a formula to place in ColC of this table that will look at the monitoring tool above and if all values for a given engine are "true", return the value that is in ColB. I've filled in the table below with the result I am trying to achieve. Engine String Status 1 ABC ABC 2 EFG Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |