Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Dilema detail
Thanks everyone, I tried Ron's formula and received a value error. The
others may be to complicated for my level. I noticed in some of the replies either dg192 is mentioned in the formulas without mention of dg193 or vice versa. They both are required. I hope this attempt at a text description helps. I do know lookup tables but I can not see how in this case it helps but am trying to understand the possibilities In the parameters of the first note it is described showing 5 IF statements: This formula is in B1. Please read on past the formula as the problem with DG193 being added to the formula with DG192 is explained. =IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5) ,C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7. 5),C6,IF(AND(DG192=5,T2<7.5),C7))))) A cell (b1) is a cell that reacts If t2 is less than 7.4 and DG192 equals 1 and if so, is then populatated with the contents of c3. It continues with the same if statement except now if DG192 equals 2 it is then populatated with the contents of c4 and so on until you reach DG192 possibly equaling 5 and populating with c7. ***The problem is that b1 must also react if t2 is greater than 7.4 and if so it must look at DG193 instead of DG192 and check if it is equal to 1,2,3,4 or 5 and if it is, place the contents of either c3, c4, c5, c6, or c7.***** The contents of the cells c1 thru c7 carry FeetPerSecond calculations that are formulated differently and are done so also by what distance t2 indicates. The controlling cell is t2 whether or not it is greater or less than 7.4 as the formula for the FeetPerSecond change once the distance passes 7.5 or is less than 7. All said and done, if it were possible to add 5 more IF statements referring to DG193 and the 5 possiblities of content, I could do it, but my apologies, that is about my level of skill and am having trouble getting past that. Appreciate any input, , , Special note, Dg192 and Dg193 will definitely be different when t2's value is above or below 7.4 but may also be different at the same t2 value. (not common but does occur) This is why the whole thing blew up. "Ron Rosenfeld" wrote: On Sun, 24 Dec 2006 07:23:00 -0800, Shu of AZ wrote: My dilema is that in the same statement you read below, I have to also ask also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF statements. Any ideas on how to reduce this statement? =IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5) ,C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7. 5),C6,IF(AND(DG192=5,T2<7.5),C7))))) There are a number of different ways of solving this problem. They vary in complexity depending on the complexity you require. Here's one way, given your parameters: =IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7), CHOOSE(DG192,D3,D4,D5,D6,D7)) You could also set up lookup tables. --ron |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Dilema detail
What if T2=7.4?
As the problem stands, this might work. =CHOOSE(IF(T2<7.4,DG192,IF(T27.4,DG193,6)),C3,C4, C5,C6,C7,"N/A") If you want to return DG192 if T2=7.4, it could be shortened to: =CHOOSE(IF(T2<=7.4,DG192,DG193),C3,C4,C5,C6,C7) or if you want DG193 if T2=7.4 =CHOOSE(IF(T2<7.4,DG192,DG193),C3,C4,C5,C6,C7) "Shu of AZ" wrote: Thanks everyone, I tried Ron's formula and received a value error. The others may be to complicated for my level. I noticed in some of the replies either dg192 is mentioned in the formulas without mention of dg193 or vice versa. They both are required. I hope this attempt at a text description helps. I do know lookup tables but I can not see how in this case it helps but am trying to understand the possibilities In the parameters of the first note it is described showing 5 IF statements: This formula is in B1. Please read on past the formula as the problem with DG193 being added to the formula with DG192 is explained. =IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5) ,C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7. 5),C6,IF(AND(DG192=5,T2<7.5),C7))))) A cell (b1) is a cell that reacts If t2 is less than 7.4 and DG192 equals 1 and if so, is then populatated with the contents of c3. It continues with the same if statement except now if DG192 equals 2 it is then populatated with the contents of c4 and so on until you reach DG192 possibly equaling 5 and populating with c7. ***The problem is that b1 must also react if t2 is greater than 7.4 and if so it must look at DG193 instead of DG192 and check if it is equal to 1,2,3,4 or 5 and if it is, place the contents of either c3, c4, c5, c6, or c7.***** The contents of the cells c1 thru c7 carry FeetPerSecond calculations that are formulated differently and are done so also by what distance t2 indicates. The controlling cell is t2 whether or not it is greater or less than 7.4 as the formula for the FeetPerSecond change once the distance passes 7.5 or is less than 7. All said and done, if it were possible to add 5 more IF statements referring to DG193 and the 5 possiblities of content, I could do it, but my apologies, that is about my level of skill and am having trouble getting past that. Appreciate any input, , , Special note, Dg192 and Dg193 will definitely be different when t2's value is above or below 7.4 but may also be different at the same t2 value. (not common but does occur) This is why the whole thing blew up. "Ron Rosenfeld" wrote: On Sun, 24 Dec 2006 07:23:00 -0800, Shu of AZ wrote: My dilema is that in the same statement you read below, I have to also ask also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF statements. Any ideas on how to reduce this statement? =IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5) ,C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7. 5),C6,IF(AND(DG192=5,T2<7.5),C7))))) There are a number of different ways of solving this problem. They vary in complexity depending on the complexity you require. Here's one way, given your parameters: =IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7), CHOOSE(DG192,D3,D4,D5,D6,D7)) You could also set up lookup tables. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Dilema detail
It can never be 7.4, only 7 and below or 7.5 and above but I tried this and
it seems to work. I'm waiting for the program to finsh running to see if the results match the user acceptance. =IF(AND(T2<7.5,OR(DG192={1,2,3,4,5})),CHOOSE(DG192 ,C3,C4,C5,C6,C7),IF(AND(T27.4,OR(DG193={1,2,3,4,5 })),CHOOSE(DG193,C3,C4,C5,C6,C7))) "JMB" wrote: What if T2=7.4? As the problem stands, this might work. =CHOOSE(IF(T2<7.4,DG192,IF(T27.4,DG193,6)),C3,C4, C5,C6,C7,"N/A") If you want to return DG192 if T2=7.4, it could be shortened to: =CHOOSE(IF(T2<=7.4,DG192,DG193),C3,C4,C5,C6,C7) or if you want DG193 if T2=7.4 =CHOOSE(IF(T2<7.4,DG192,DG193),C3,C4,C5,C6,C7) "Shu of AZ" wrote: Thanks everyone, I tried Ron's formula and received a value error. The others may be to complicated for my level. I noticed in some of the replies either dg192 is mentioned in the formulas without mention of dg193 or vice versa. They both are required. I hope this attempt at a text description helps. I do know lookup tables but I can not see how in this case it helps but am trying to understand the possibilities In the parameters of the first note it is described showing 5 IF statements: This formula is in B1. Please read on past the formula as the problem with DG193 being added to the formula with DG192 is explained. =IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5) ,C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7. 5),C6,IF(AND(DG192=5,T2<7.5),C7))))) A cell (b1) is a cell that reacts If t2 is less than 7.4 and DG192 equals 1 and if so, is then populatated with the contents of c3. It continues with the same if statement except now if DG192 equals 2 it is then populatated with the contents of c4 and so on until you reach DG192 possibly equaling 5 and populating with c7. ***The problem is that b1 must also react if t2 is greater than 7.4 and if so it must look at DG193 instead of DG192 and check if it is equal to 1,2,3,4 or 5 and if it is, place the contents of either c3, c4, c5, c6, or c7.***** The contents of the cells c1 thru c7 carry FeetPerSecond calculations that are formulated differently and are done so also by what distance t2 indicates. The controlling cell is t2 whether or not it is greater or less than 7.4 as the formula for the FeetPerSecond change once the distance passes 7.5 or is less than 7. All said and done, if it were possible to add 5 more IF statements referring to DG193 and the 5 possiblities of content, I could do it, but my apologies, that is about my level of skill and am having trouble getting past that. Appreciate any input, , , Special note, Dg192 and Dg193 will definitely be different when t2's value is above or below 7.4 but may also be different at the same t2 value. (not common but does occur) This is why the whole thing blew up. "Ron Rosenfeld" wrote: On Sun, 24 Dec 2006 07:23:00 -0800, Shu of AZ wrote: My dilema is that in the same statement you read below, I have to also ask also if T27.4 and dg193=1,2,3,4,or 5, but that would be too many IF statements. Any ideas on how to reduce this statement? =IF(AND(DG192=1,T2<7.5),C3,IF(AND(DG192=2,T2<7.5) ,C4,IF(AND(DG192=3,T2<7.5),C5,IF(AND(DG192=4,T2<7. 5),C6,IF(AND(DG192=5,T2<7.5),C7))))) There are a number of different ways of solving this problem. They vary in complexity depending on the complexity you require. Here's one way, given your parameters: =IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7), CHOOSE(DG192,D3,D4,D5,D6,D7)) You could also set up lookup tables. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Dilema detail
TRY
=IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7),CHOOSE(DG1 93,C3,C4,C5,C6,C7)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Dilema detail
I think you could shorten it to
=CHOOSE(IF(T2<7.5, DG192, DG193),C3,C4,C5,C6,C7) "excelent" wrote: TRY =IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7),CHOOSE(DG1 93,C3,C4,C5,C6,C7)) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Dilema detail
As simple as
=Indirect("C"&If(T2<7.5,DG192,DG193)+2) If you don't want to protect against erroneous values in DG192 and DG193 -- Regards, Tom Ogilvy "JMB" wrote in message ... I think you could shorten it to =CHOOSE(IF(T2<7.5, DG192, DG193),C3,C4,C5,C6,C7) "excelent" wrote: TRY =IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7),CHOOSE(DG1 93,C3,C4,C5,C6,C7)) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Dilema detail
Quite true. I sort of assume since there seems to be some controls for T2
(OP said it could never be 7.4) there are also controls in place for DG192 and DG193 (which may/may not be the case). Always a pleasure, Tom. Happy Holidays! "Tom Ogilvy" wrote: As simple as =Indirect("C"&If(T2<7.5,DG192,DG193)+2) If you don't want to protect against erroneous values in DG192 and DG193 -- Regards, Tom Ogilvy "JMB" wrote in message ... I think you could shorten it to =CHOOSE(IF(T2<7.5, DG192, DG193),C3,C4,C5,C6,C7) "excelent" wrote: TRY =IF(T2<7.5,CHOOSE(DG192,C3,C4,C5,C6,C7),CHOOSE(DG1 93,C3,C4,C5,C6,C7)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Disable the Show/Hide Detail upon refresh | Excel Discussion (Misc queries) | |||
day of week without detail | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
working with sub-totaled data without the detail | Excel Worksheet Functions | |||
Show detail | Excel Worksheet Functions |