Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Hi,
Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
I think your first formula is in error. You are in effect only looking for
any value in C1575 that is greater than "18:00:00" your AND portion should be: AND("06:00:00"<C1575,C1575<"18:00:00") if you are wanting to include only the times between 6am and 6pm (non inclusive). -- John C "Joco" wrote: Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
=IF(AND(--"06:00:00"<C1575,C1575<--"18:00:00"),5,3)
=SUMPRODUCT(--(ISNUMBER(MATCH(E10:E1600,E1609:E1615,0))),K10:K16 00) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Hi John thanks for your reply
I have changed the one difference in the formulay, but irrespective of the time the answer is always shown as 3 I was actually looking to highlite thoose after 18:00 and before 06:00 Joco (London) "John C" wrote: I think your first formula is in error. You are in effect only looking for any value in C1575 that is greater than "18:00:00" your AND portion should be: AND("06:00:00"<C1575,C1575<"18:00:00") if you are wanting to include only the times between 6am and 6pm (non inclusive). -- John C "Joco" wrote: Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Oh, ok.
See Bob's solution, need to ensure the times in quotes are treated as numbers, and the easy way to do it is with the -- in front of the quotes. In regards to your other formula. You have realized that it is an array, and you still have to sum the array formula. =SUM(SUMIF(Range1,RangeTest,Range2)) Since this is an array formula, remember to press CTRL+SHIFT+ENTER to enter the formula. -- John C "Joco" wrote: Hi John thanks for your reply I have changed the one difference in the formulay, but irrespective of the time the answer is always shown as 3 I was actually looking to highlite thoose after 18:00 and before 06:00 Joco (London) "John C" wrote: I think your first formula is in error. You are in effect only looking for any value in C1575 that is greater than "18:00:00" your AND portion should be: AND("06:00:00"<C1575,C1575<"18:00:00") if you are wanting to include only the times between 6am and 6pm (non inclusive). -- John C "Joco" wrote: Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Even array entered it doesn't work. My SP formula is one way.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John C" <johnc@stateofdenial wrote in message ... Oh, ok. See Bob's solution, need to ensure the times in quotes are treated as numbers, and the easy way to do it is with the -- in front of the quotes. In regards to your other formula. You have realized that it is an array, and you still have to sum the array formula. =SUM(SUMIF(Range1,RangeTest,Range2)) Since this is an array formula, remember to press CTRL+SHIFT+ENTER to enter the formula. -- John C "Joco" wrote: Hi John thanks for your reply I have changed the one difference in the formulay, but irrespective of the time the answer is always shown as 3 I was actually looking to highlite thoose after 18:00 and before 06:00 Joco (London) "John C" wrote: I think your first formula is in error. You are in effect only looking for any value in C1575 that is greater than "18:00:00" your AND portion should be: AND("06:00:00"<C1575,C1575<"18:00:00") if you are wanting to include only the times between 6am and 6pm (non inclusive). -- John C "Joco" wrote: Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Worked just fine for me with array entered.
-- John C "Bob Phillips" wrote: Even array entered it doesn't work. My SP formula is one way. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John C" <johnc@stateofdenial wrote in message ... Oh, ok. See Bob's solution, need to ensure the times in quotes are treated as numbers, and the easy way to do it is with the -- in front of the quotes. In regards to your other formula. You have realized that it is an array, and you still have to sum the array formula. =SUM(SUMIF(Range1,RangeTest,Range2)) Since this is an array formula, remember to press CTRL+SHIFT+ENTER to enter the formula. -- John C "Joco" wrote: Hi John thanks for your reply I have changed the one difference in the formulay, but irrespective of the time the answer is always shown as 3 I was actually looking to highlite thoose after 18:00 and before 06:00 Joco (London) "John C" wrote: I think your first formula is in error. You are in effect only looking for any value in C1575 that is greater than "18:00:00" your AND portion should be: AND("06:00:00"<C1575,C1575<"18:00:00") if you are wanting to include only the times between 6am and 6pm (non inclusive). -- John C "Joco" wrote: Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Bob Hi and thanks for your prompt responce, both forumals work ok
I have tried to amend the SUMPRODUCT formular for something else without success I am trying to put a symbol in a collumn where a cell E14 equals names in P17:P24, all fields are text names. If you could assist me with this it would be appreciated Thanks Joco (London) "Bob Phillips" wrote: =IF(AND(--"06:00:00"<C1575,C1575<--"18:00:00"),5,3) =SUMPRODUCT(--(ISNUMBER(MATCH(E10:E1600,E1609:E1615,0))),K10:K16 00) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Not sure I understand. All ranges must be similar sized, you can't have a 10
row range for one test and a 25 row range for another. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Bob Hi and thanks for your prompt responce, both forumals work ok I have tried to amend the SUMPRODUCT formular for something else without success I am trying to put a symbol in a collumn where a cell E14 equals names in P17:P24, all fields are text names. If you could assist me with this it would be appreciated Thanks Joco (London) "Bob Phillips" wrote: =IF(AND(--"06:00:00"<C1575,C1575<--"18:00:00"),5,3) =SUMPRODUCT(--(ISNUMBER(MATCH(E10:E1600,E1609:E1615,0))),K10:K16 00) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
My apologies, I missed your addition of SUM to the SUMIF.
-- __________________________________ HTH Bob "John C" <johnc@stateofdenial wrote in message ... Worked just fine for me with array entered. -- John C "Bob Phillips" wrote: Even array entered it doesn't work. My SP formula is one way. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John C" <johnc@stateofdenial wrote in message ... Oh, ok. See Bob's solution, need to ensure the times in quotes are treated as numbers, and the easy way to do it is with the -- in front of the quotes. In regards to your other formula. You have realized that it is an array, and you still have to sum the array formula. =SUM(SUMIF(Range1,RangeTest,Range2)) Since this is an array formula, remember to press CTRL+SHIFT+ENTER to enter the formula. -- John C "Joco" wrote: Hi John thanks for your reply I have changed the one difference in the formulay, but irrespective of the time the answer is always shown as 3 I was actually looking to highlite thoose after 18:00 and before 06:00 Joco (London) "John C" wrote: I think your first formula is in error. You are in effect only looking for any value in C1575 that is greater than "18:00:00" your AND portion should be: AND("06:00:00"<C1575,C1575<"18:00:00") if you are wanting to include only the times between 6am and 6pm (non inclusive). -- John C "Joco" wrote: Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Bob Thanks,
Column A Column B Column C Formular Result Smith Formula Brown Jones Formula Davis *** Wilson Formula Jones Harris Formula Wilson *** York Formula Carlton Formula Brown Formula *** I am trying to put a symbol in column B if the name appears in column C. Column A will be say 1,000 lines but column only 20 or so. Hope this explains better Thanks Joco (London) "Bob Phillips" wrote: Not sure I understand. All ranges must be similar sized, you can't have a 10 row range for one test and a 25 row range for another. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Bob Hi and thanks for your prompt responce, both forumals work ok I have tried to amend the SUMPRODUCT formular for something else without success I am trying to put a symbol in a collumn where a cell E14 equals names in P17:P24, all fields are text names. If you could assist me with this it would be appreciated Thanks Joco (London) "Bob Phillips" wrote: =IF(AND(--"06:00:00"<C1575,C1575<--"18:00:00"),5,3) =SUMPRODUCT(--(ISNUMBER(MATCH(E10:E1600,E1609:E1615,0))),K10:K16 00) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Is this what you mean?
=IF(ISNUMBER(MATCH(A2,C:C,0)),"symbol", other_formula) -- __________________________________ HTH Bob "Joco" wrote in message ... Bob Thanks, Column A Column B Column C Formular Result Smith Formula Brown Jones Formula Davis *** Wilson Formula Jones Harris Formula Wilson *** York Formula Carlton Formula Brown Formula *** I am trying to put a symbol in column B if the name appears in column C. Column A will be say 1,000 lines but column only 20 or so. Hope this explains better Thanks Joco (London) "Bob Phillips" wrote: Not sure I understand. All ranges must be similar sized, you can't have a 10 row range for one test and a 25 row range for another. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Bob Hi and thanks for your prompt responce, both forumals work ok I have tried to amend the SUMPRODUCT formular for something else without success I am trying to put a symbol in a collumn where a cell E14 equals names in P17:P24, all fields are text names. If you could assist me with this it would be appreciated Thanks Joco (London) "Bob Phillips" wrote: =IF(AND(--"06:00:00"<C1575,C1575<--"18:00:00"),5,3) =SUMPRODUCT(--(ISNUMBER(MATCH(E10:E1600,E1609:E1615,0))),K10:K16 00) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF with nested AND
Bob Hi,
Thank you for the three formulas, which do exactly what I require. I will now be able to replace some very long and unwieldly formulas and procedures with thoose you have given me, which will reduce the size, and simplfy my spreedsheets. Thanks once again Joco (London) "Bob Phillips" wrote: Is this what you mean? =IF(ISNUMBER(MATCH(A2,C:C,0)),"symbol", other_formula) -- __________________________________ HTH Bob "Joco" wrote in message ... Bob Thanks, Column A Column B Column C Formular Result Smith Formula Brown Jones Formula Davis *** Wilson Formula Jones Harris Formula Wilson *** York Formula Carlton Formula Brown Formula *** I am trying to put a symbol in column B if the name appears in column C. Column A will be say 1,000 lines but column only 20 or so. Hope this explains better Thanks Joco (London) "Bob Phillips" wrote: Not sure I understand. All ranges must be similar sized, you can't have a 10 row range for one test and a 25 row range for another. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Bob Hi and thanks for your prompt responce, both forumals work ok I have tried to amend the SUMPRODUCT formular for something else without success I am trying to put a symbol in a collumn where a cell E14 equals names in P17:P24, all fields are text names. If you could assist me with this it would be appreciated Thanks Joco (London) "Bob Phillips" wrote: =IF(AND(--"06:00:00"<C1575,C1575<--"18:00:00"),5,3) =SUMPRODUCT(--(ISNUMBER(MATCH(E10:E1600,E1609:E1615,0))),K10:K16 00) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joco" wrote in message ... Hi, Can you please tell me how to put a symbol on a line, if a time is before or after a specified time. I have tried the following which is a valid formula, but does not give me the correct answer. =IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is formatted to monotype sorts to give either a tick or a cross) I am also trying to produce an IF formula that looks at a block of cells, I have tried the following, which only works for the first cell in the block =SUMIF(E10:E1600,E1609:E1615,(K10:K1600)) The E1609:E1615 contains text names Any help would be really appreciated, thanks Joco (London) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested if | Excel Worksheet Functions | |||
nested ifs not enough | Excel Discussion (Misc queries) | |||
nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
Nested IF | Excel Worksheet Functions | |||
Nested if | Excel Worksheet Functions |