| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF OR problem
Not sure where to start with this formula but I need a single formula that
results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Look up IF(), OR(), and AND(). Those three are the key. For each
desired result, I would have an if statement: if(e3=u6,120,0) if(e3=u8,150,0) if(and(g3="com",or(e3=u10,e3=u12,e3=u14,e3=u16)),3 50,0) You get the idea. Once you have each formula working the way you want, simply add them together: if(e3=u6,120,0)+if(e3=u8,150,0)+if(and(g3="com",or (e3=u10,e3=u12,e3=u14,e3=u16)),350,0) Any combination which fails will add zero to the result, the one which matches will add 120, 150, or whatever is in the IF()statement for the true result. Look at my third example carefully to understand how or /and combinations work. You are using AND primarily, and all of your possible ORs are wrapped up together with the and. At least one of the equations in the Or() must be true, and the standalone g3="com" must be true, for that if() statement to be considered true, and yield 350. Make sense? Phil ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
You should be able to nest your statement like this:
=IF(E3="U6",120,IF(E3="U8",150,IF(E3="u18",375,IF( G3="com",350,IF(G3="dev",380,IF(G3="Tier",410,0))) )))-- If this helps, please remember to click yes. "ksean" wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Starting in cell b3 the following is entered
U6 120 U8 150 U18 375 com 350 dev 380 Tier 410 Then the following equation was added =IF(ISBLANK(G3),VLOOKUP(E3,$B$3:$C$5,2,FALSE),VLOO KUP(G3,$B$6:$C$8,2,FALSE)) Copy down as needed. -- Wag more, bark less "ksean" wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Try this:
=IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND (G3="com",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,350,0) + IF(AND (G3="dev",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,380,0) + IF(AND (G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3="U16") ),410,0) Hope this helps. Pete On Dec 3, 9:16*pm, ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or * * * * = "U8" then 150 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or * * * * = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
The basic logic isn't clear from your example, but you can use nested
Select Case statements to test the values. Adjust the following code as desired. Dim Result As Variant Select Case Range("E3") Case "U6" Result = 120 Case "U8" Result = 150 Case "U10", "U12", "U14" Select Case Range("G3") Case "com" Result = 350 Case "dev" Result = 380 Case "tier" Result = 410 End Select Case Else Result = "none of the above" End Select Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 3 Dec 2009 13:16:01 -0800, ksean wrote: If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Paul,
You missed part of the scope of the question. Kerry "Paul C" wrote: You should be able to nest your statement like this: =IF(E3="U6",120,IF(E3="U8",150,IF(E3="u18",375,IF( G3="com",350,IF(G3="dev",380,IF(G3="Tier",410,0))) )))-- If this helps, please remember to click yes. "ksean" wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Brad,
Interesting approach however you missed part of the scope of the problem. The possible combinations are U6=120 U8=150 U10+com=350 U10+dev=380 U10+tier=410 U12+com=350 U12+dev=380 U12+tier=410 U14+com=350 U14+dev=380 U14+tier=410 U16+com=350 U16+dev=380 U16+tier=410 U18=375 Kerry "Brad" wrote: Starting in cell b3 the following is entered U6 120 U8 150 U18 375 com 350 dev 380 Tier 410 Then the following equation was added =IF(ISBLANK(G3),VLOOKUP(E3,$B$3:$C$5,2,FALSE),VLOO KUP(G3,$B$6:$C$8,2,FALSE)) Copy down as needed. -- Wag more, bark less "ksean" wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Phil Smith & Pete_UK, Your answers worked great however now I need to make the situation a whole bunch more complicated. In =IF(E3="U6",120,0) the 120 refers to a value that is date sensitive and in cells M1, N1 or O1. For example: 120 would be the answer from Aug 1, 09 to Aug 25, 09 in cell M1 130 would be the answer from August 26, 09 to Sept 3, 09 in cell N1 150 would be the answer for any date after Sept 3, 09 in cell O1 The reference date would be in cell P1 K L M N O Aug 1 - 25 Aug 26 - Sept 3 Sept 4 + 1 U6 Comm 120 130 150 2 U8 Comm 175 185 205 3 U10-U16 Comm 280 290 330 4 U10-U16 Dev 340 350 395 5 U10-U16 Tier 350 360 405 6 U18 Comm 300 310 350 7 8 8/10/09 If the reference date in cell K8 was August 10, 09 then the value in question would be found in cell M1 however if the reference date was August 28, 09 then the value in question would be found in cell N1 The same thing would also have to happen in the other sections of the formula thus making the formula a whole bunch more complicated. Any thoughts on how I would incorporate this back into the formula. Thanks Kerry "Pete_UK" wrote: Try this: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND (G3="com",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,350,0) + IF(AND (G3="dev",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,380,0) + IF(AND (G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3="U16") ),410,0) Hope this helps. Pete On Dec 3, 9:16 pm, ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Chip,
Your approach is brand new to me and I find it quite interesting, how can I apply it to my spread sheet as I am not familiar with Select Case statements? I am very eager to learn however! Kerry "Chip Pearson" wrote: The basic logic isn't clear from your example, but you can use nested Select Case statements to test the values. Adjust the following code as desired. Dim Result As Variant Select Case Range("E3") Case "U6" Result = 120 Case "U8" Result = 150 Case "U10", "U12", "U14" Select Case Range("G3") Case "com" Result = 350 Case "dev" Result = 380 Case "tier" Result = 410 End Select Case Else Result = "none of the above" End Select Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 3 Dec 2009 13:16:01 -0800, ksean wrote: If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 . |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
I thought you were working in VBA code, so I provided a VBA solution.
It isn't a formula. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 3 Dec 2009 16:04:01 -0800, ksean wrote: Chip, Your approach is brand new to me and I find it quite interesting, how can I apply it to my spread sheet as I am not familiar with Select Case statements? I am very eager to learn however! Kerry "Chip Pearson" wrote: The basic logic isn't clear from your example, but you can use nested Select Case statements to test the values. Adjust the following code as desired. Dim Result As Variant Select Case Range("E3") Case "U6" Result = 120 Case "U8" Result = 150 Case "U10", "U12", "U14" Select Case Range("G3") Case "com" Result = 350 Case "dev" Result = 380 Case "tier" Result = 410 End Select Case Else Result = "none of the above" End Select Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 3 Dec 2009 13:16:01 -0800, ksean wrote: If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 . |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Is the reference date in P1 or K8 ? You quote both.
You could do this with an INDEX/MATCH combination, but it's getting a bit late here so I'll have to leave that till tomorrow. Let me know if you still need help then. Pete On Dec 3, 11:58*pm, ksean wrote: Phil Smith & Pete_UK, Your answers worked great however now I need to make the situation a whole bunch more complicated. In *=IF(E3="U6",120,0) *the 120 refers to a value that is date sensitive and in cells M1, N1 or O1. For example: 120 would be the answer from Aug 1, 09 to Aug 25, 09 in cell M1 130 would be the answer from August 26, 09 to Sept 3, 09 in cell N1 150 would be the answer for any date after Sept 3, 09 in cell O1 The reference date would be in cell P1 * * * * K * * * L * * * M * * * N * * * O * * * * * * * * * * * * Aug 1 - 25 * * *Aug 26 - Sept 3 Sept 4 + 1 * * * U6 * * *Comm * *120 * * 130 * * 150 2 * * * U8 * * *Comm * *175 * * 185 * * 205 3 * * * U10-U16 Comm * *280 * * 290 * * 330 4 * * * U10-U16 Dev * * 340 * * 350 * * 395 5 * * * U10-U16 Tier * *350 * * 360 * * 405 6 * * * U18 * * Comm * *300 * * 310 * * 350 7 * * * * * * * * * * * * * * * * * * * * 8 * * * 8/10/09 * * * * * * * * * * * * * If the reference date in cell K8 was August 10, 09 then the value in question would be found in cell M1 however if the reference date was August 28, 09 then the value in question would be found in cell N1 The same thing would also have to happen in the other sections of the formula thus making the formula a whole bunch more complicated. Any thoughts on how I would incorporate this back into the formula. Thanks Kerry "Pete_UK" wrote: Try this: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND (G3="com",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,350,0) + IF(AND (G3="dev",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,380,0) + IF(AND (G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3="U16") ),410,0) Hope this helps. Pete On Dec 3, 9:16 pm, ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or * * * * = "U8" then 150 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or * * * * = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. .- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Pete,
I moved it from P1 to K8 for the purpose of problem, sorry for the confusion. I still need help please. Kerry "Pete_UK" wrote: Is the reference date in P1 or K8 ? You quote both. You could do this with an INDEX/MATCH combination, but it's getting a bit late here so I'll have to leave that till tomorrow. Let me know if you still need help then. Pete On Dec 3, 11:58 pm, ksean wrote: Phil Smith & Pete_UK, Your answers worked great however now I need to make the situation a whole bunch more complicated. In =IF(E3="U6",120,0) the 120 refers to a value that is date sensitive and in cells M1, N1 or O1. For example: 120 would be the answer from Aug 1, 09 to Aug 25, 09 in cell M1 130 would be the answer from August 26, 09 to Sept 3, 09 in cell N1 150 would be the answer for any date after Sept 3, 09 in cell O1 The reference date would be in cell P1 K L M N O Aug 1 - 25 Aug 26 - Sept 3 Sept 4 + 1 U6 Comm 120 130 150 2 U8 Comm 175 185 205 3 U10-U16 Comm 280 290 330 4 U10-U16 Dev 340 350 395 5 U10-U16 Tier 350 360 405 6 U18 Comm 300 310 350 7 8 8/10/09 If the reference date in cell K8 was August 10, 09 then the value in question would be found in cell M1 however if the reference date was August 28, 09 then the value in question would be found in cell N1 The same thing would also have to happen in the other sections of the formula thus making the formula a whole bunch more complicated. Any thoughts on how I would incorporate this back into the formula. Thanks Kerry "Pete_UK" wrote: Try this: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND (G3="com",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,350,0) + IF(AND (G3="dev",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,380,0) + IF(AND (G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3="U16") ),410,0) Hope this helps. Pete On Dec 3, 9:16 pm, ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. .- Hide quoted text - - Show quoted text - . |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
This would be my first attempt at working with VBA code and I am willing to
learn if you have the patience. I have posted your suggestion on the page in question however it is coming back to me with a compile error. How do I assign a line number or label or statement or end of statement? If it is too complicated for this form just say so. Thanks Kerry "Chip Pearson" wrote: I thought you were working in VBA code, so I provided a VBA solution. It isn't a formula. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 3 Dec 2009 16:04:01 -0800, ksean wrote: Chip, Your approach is brand new to me and I find it quite interesting, how can I apply it to my spread sheet as I am not familiar with Select Case statements? I am very eager to learn however! Kerry "Chip Pearson" wrote: The basic logic isn't clear from your example, but you can use nested Select Case statements to test the values. Adjust the following code as desired. Dim Result As Variant Select Case Range("E3") Case "U6" Result = 120 Case "U8" Result = 150 Case "U10", "U12", "U14" Select Case Range("G3") Case "com" Result = 350 Case "dev" Result = 380 Case "tier" Result = 410 End Select Case Else Result = "none of the above" End Select Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 3 Dec 2009 13:16:01 -0800, ksean wrote: If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 . . |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
OK, now you are getting far more complicated. May I ask the more
general purpose of what you are trying to do here? Adding this additional date into the mix is really the same answer, in that you can use the same basic concept. The Only difference is instead of 120, or 150, you would replace that single number with a nested if: assuming say d4 is the date field you are testing... if(and(d4"1/1/09",d4<"6/1/09",150,if(...))) The problem I see here is that you are getting VERY complicated for something to be handled discretely, (hard coded formulas for each possible outcome.) Your original example was doable, and about the limits of what I would handle in a single formula. I broke each one up into it's own if statement, rather then nested ifs as Paul C suggested, simply because it is much easier to troubleshoot and see what is going on. You could find yourself in a world of hurt if you took the same basic apporach for something even more complicated, like your multiple data issue, and then someone decided you need to change your date ranges. I would honestly forego the single formula approach, and break them up a little bit. Create 1 or more coloums that handle the Date range question, 1 or more that handle the cell1 = cell3 or cell5 or cell7 question, (turning each into a true false result, 1 means it matches, 0 it does not. Then a formula that goes through those and makes the final determinationas to what value you want. It will make it much easier to troubleshoot or make changes, as you would just go to the coloum wit the piece that is affected, and edit it there. he only reason to make it a single formula is for appearances, and you could put all of that work into a seperate worksheet, and hide it, or hide the coloumns involved, so appearance would not be an issue. If we knew better what you are actually working with and trying to accomplish, we might be able to suggest a completely different approach that would be more suitable. Phil Smith wrote: Look up IF(), OR(), and AND(). Those three are the key. For each desired result, I would have an if statement: if(e3=u6,120,0) if(e3=u8,150,0) if(and(g3="com",or(e3=u10,e3=u12,e3=u14,e3=u16)),3 50,0) You get the idea. Once you have each formula working the way you want, simply add them together: if(e3=u6,120,0)+if(e3=u8,150,0)+if(and(g3="com",or (e3=u10,e3=u12,e3=u14,e3=u16)),350,0) Any combination which fails will add zero to the result, the one which matches will add 120, 150, or whatever is in the IF()statement for the true result. Look at my third example carefully to understand how or /and combinations work. You are using AND primarily, and all of your possible ORs are wrapped up together with the and. At least one of the equations in the Or() must be true, and the standalone g3="com" must be true, for that if() statement to be considered true, and yield 350. Make sense? Phil ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
|
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
This is the formula that I gave you:
=IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND(G3="com",OR(E3="U10",E3="U12",E3="U14",E3=" U16")),350,0) + IF(AND(G3="dev",OR(E3="U10",E3="U12",E3="U14",E3=" U16")),380,0) + IF(AND(G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3= "U16")),410,0) I've manually split it so that it is easier to read. On the first row above, you want the 120 in the first IF to be replaced with a formula that will pick from M1:O1 depending on the date. Similarly, the 150 in the second IF is chosen from M2:O2 depending on the date, although the values you show are 175, 185 and 205. The 375 in the third IF should come from row 6, but there you show the values 300, 310 and 350 in your table. The 4th IF relates to your row 3, where the values are 280, 290, 330, rather than the 350 currently in the formula. The 380 in the 5th IF should come from row 4, where you have the values 340, 350 and 395, and the final IF has a value of 410 in the formula which should be replaced by one of the values from row 5, i.e. 350, 360 or 405. Only your know if these values are correct, and why they differ from what you first posted, but I spell them out here so that you can see clearly what has to be changed. In the first IF you can change the 120 to this: INDEX(M1:O6,1,IF(K8<=--"Aug 25, 09",1,IF(K8<=--"Sept 3, 09",2,3)) This is not fully testing the value in K8, but assumes that it is validated elsewhere. Now, you can replace each of those numbers referred to above with a very similar formula - the only difference is that you replace the 1 after the O6, with the row number that the combination relates to. As Phil states above, this will be a horrendous formula, and I certainly wouldn't like to amend it if things change in the future. Hope this helps. Pete On Dec 4, 5:50*pm, ksean wrote: Pete, I moved it from P1 to K8 for the purpose of problem, sorry for the confusion. I still need help please. Kerry "Pete_UK" wrote: Is the reference date in P1 or K8 ? You quote both. You could do this with an INDEX/MATCH combination, but it's getting a bit late here so I'll have to leave that till tomorrow. Let me know if you still need help then. Pete On Dec 3, 11:58 pm, ksean wrote: Phil Smith & Pete_UK, Your answers worked great however now I need to make the situation a whole bunch more complicated. In *=IF(E3="U6",120,0) *the 120 refers to a value that is date sensitive and in cells M1, N1 or O1. For example: 120 would be the answer from Aug 1, 09 to Aug 25, 09 in cell M1 130 would be the answer from August 26, 09 to Sept 3, 09 in cell N1 150 would be the answer for any date after Sept 3, 09 in cell O1 The reference date would be in cell P1 * * * * K * * * L * * * M * * * N * * * O * * * * * * * * * * * * Aug 1 - 25 * * *Aug 26 - Sept 3 Sept 4 + 1 * * * U6 * * *Comm * *120 * * 130 * * 150 2 * * * U8 * * *Comm * *175 * * 185 * * 205 3 * * * U10-U16 Comm * *280 * * 290 * * 330 4 * * * U10-U16 Dev * * 340 * * 350 * * 395 5 * * * U10-U16 Tier * *350 * * 360 * * 405 6 * * * U18 * * Comm * *300 * * 310 * * 350 7 * * * * * * * * * * * * * * * * * * * * 8 * * * 8/10/09 * * * * * * * * * * * * * If the reference date in cell K8 was August 10, 09 then the value in question would be found in cell M1 however if the reference date was August 28, 09 then the value in question would be found in cell N1 The same thing would also have to happen in the other sections of the formula thus making the formula a whole bunch more complicated. Any thoughts on how I would incorporate this back into the formula. Thanks Kerry "Pete_UK" wrote: Try this: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND (G3="com",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,350,0) + IF(AND (G3="dev",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,380,0) + IF(AND (G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3="U16") ),410,0) Hope this helps. Pete On Dec 3, 9:16 pm, ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or * * * * = "U8" then 150 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or * * * * = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Sorry, I missed a closing bracket at the end of that INDEX snippet -
there should be 3. Pete On Dec 4, 9:06*pm, Pete_UK wrote: This is the formula that I gave you: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND(G3="com",OR(E3="U10",E3="U12",E3="U14",E3=" U16")),350,0) + IF(AND(G3="dev",OR(E3="U10",E3="U12",E3="U14",E3=" U16")),380,0) + IF(AND(G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3= "U16")),410,0) I've manually split it so that it is easier to read. On the first row above, you want the 120 in the first IF to be replaced with a formula that will pick from M1:O1 depending on the date. Similarly, the 150 in the second IF is chosen from M2:O2 depending on the date, although the values you show are 175, 185 and 205. The 375 in the third IF should come from row 6, but there you show the values 300, 310 and 350 in your table. The 4th IF relates to your row 3, where the values are 280, 290, 330, rather than the 350 currently in the formula. The 380 in the 5th IF should come from row 4, where you have the values 340, 350 and 395, and the final IF has a value of 410 in the formula which should be replaced by one of the values from row 5, i.e. 350, 360 or 405. Only your know if these values are correct, and why they differ from what you first posted, but I spell them out here so that you can see clearly what has to be changed. In the first IF you can change the 120 to this: INDEX(M1:O6,1,IF(K8<=--"Aug 25, 09",1,IF(K8<=--"Sept 3, 09",2,3)) This is not fully testing the value in K8, but assumes that it is validated elsewhere. Now, you can replace each of those numbers referred to above with a very similar formula - the only difference is that you replace the 1 after the O6, with the row number that the combination relates to. As Phil states above, this will be a horrendous formula, and I certainly wouldn't like to amend it if things change in the future. Hope this helps. Pete On Dec 4, 5:50*pm, ksean wrote: Pete, I moved it from P1 to K8 for the purpose of problem, sorry for the confusion. I still need help please. Kerry "Pete_UK" wrote: Is the reference date in P1 or K8 ? You quote both. You could do this with an INDEX/MATCH combination, but it's getting a bit late here so I'll have to leave that till tomorrow. Let me know if you still need help then. Pete On Dec 3, 11:58 pm, ksean wrote: Phil Smith & Pete_UK, Your answers worked great however now I need to make the situation a whole bunch more complicated. In *=IF(E3="U6",120,0) *the 120 refers to a value that is date sensitive and in cells M1, N1 or O1. For example: 120 would be the answer from Aug 1, 09 to Aug 25, 09 in cell M1 130 would be the answer from August 26, 09 to Sept 3, 09 in cell N1 150 would be the answer for any date after Sept 3, 09 in cell O1 The reference date would be in cell P1 * * * * K * * * L * * * M * * * N * * * O * * * * * * * * * * * * Aug 1 - 25 * * *Aug 26 - Sept 3 Sept 4 + 1 * * * U6 * * *Comm * *120 * * 130 * * 150 2 * * * U8 * * *Comm * *175 * * 185 * * 205 3 * * * U10-U16 Comm * *280 * * 290 * * 330 4 * * * U10-U16 Dev * * 340 * * 350 * * 395 5 * * * U10-U16 Tier * *350 * * 360 * * 405 6 * * * U18 * * Comm * *300 * * 310 * * 350 7 * * * * * * * * * * * * * * * * * * * * 8 * * * 8/10/09 * * * * * * * * * * * * * If the reference date in cell K8 was August 10, 09 then the value in question would be found in cell M1 however if the reference date was August 28, 09 then the value in question would be found in cell N1 The same thing would also have to happen in the other sections of the formula thus making the formula a whole bunch more complicated. Any thoughts on how I would incorporate this back into the formula. Thanks Kerry "Pete_UK" wrote: Try this: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND (G3="com",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,350,0) + IF(AND (G3="dev",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,380,0) + IF(AND (G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3="U16") ),410,0) Hope this helps. Pete On Dec 3, 9:16 pm, ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or * * * * = "U8" then 150 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or * * * * = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
I disagree, The "U" code doesn't matter just the word
Unless you have "words" in U6, u8 or u18 -- Wag more, bark less "ksean" wrote: Brad, Interesting approach however you missed part of the scope of the problem. The possible combinations are U6=120 U8=150 U10+com=350 U10+dev=380 U10+tier=410 U12+com=350 U12+dev=380 U12+tier=410 U14+com=350 U14+dev=380 U14+tier=410 U16+com=350 U16+dev=380 U16+tier=410 U18=375 Kerry "Brad" wrote: Starting in cell b3 the following is entered U6 120 U8 150 U18 375 com 350 dev 380 Tier 410 Then the following equation was added =IF(ISBLANK(G3),VLOOKUP(E3,$B$3:$C$5,2,FALSE),VLOO KUP(G3,$B$6:$C$8,2,FALSE)) Copy down as needed. -- Wag more, bark less "ksean" wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. |
|
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Pete,
Sorry for the data mixup, I actually mixed up two little sample tables I was working with. Once I corrected for the < error in the last IF statement, it should have been your index formula works great and yes this is going to turn in to one horrendous formula. As Phil indicated I think I am going to spend a little time rethinking the diretion I am going with this formula to see if I can simplify my approach Thanks, Kerry "Pete_UK" wrote: Sorry, I missed a closing bracket at the end of that INDEX snippet - there should be 3. Pete On Dec 4, 9:06 pm, Pete_UK wrote: This is the formula that I gave you: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND(G3="com",OR(E3="U10",E3="U12",E3="U14",E3=" U16")),350,0) + IF(AND(G3="dev",OR(E3="U10",E3="U12",E3="U14",E3=" U16")),380,0) + IF(AND(G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3= "U16")),410,0) I've manually split it so that it is easier to read. On the first row above, you want the 120 in the first IF to be replaced with a formula that will pick from M1:O1 depending on the date. Similarly, the 150 in the second IF is chosen from M2:O2 depending on the date, although the values you show are 175, 185 and 205. The 375 in the third IF should come from row 6, but there you show the values 300, 310 and 350 in your table. The 4th IF relates to your row 3, where the values are 280, 290, 330, rather than the 350 currently in the formula. The 380 in the 5th IF should come from row 4, where you have the values 340, 350 and 395, and the final IF has a value of 410 in the formula which should be replaced by one of the values from row 5, i.e. 350, 360 or 405. Only your know if these values are correct, and why they differ from what you first posted, but I spell them out here so that you can see clearly what has to be changed. In the first IF you can change the 120 to this: INDEX(M1:O6,1,IF(K8<=--"Aug 25, 09",1,IF(K8<=--"Sept 3, 09",2,3)) This is not fully testing the value in K8, but assumes that it is validated elsewhere. Now, you can replace each of those numbers referred to above with a very similar formula - the only difference is that you replace the 1 after the O6, with the row number that the combination relates to. As Phil states above, this will be a horrendous formula, and I certainly wouldn't like to amend it if things change in the future. Hope this helps. Pete On Dec 4, 5:50 pm, ksean wrote: Pete, I moved it from P1 to K8 for the purpose of problem, sorry for the confusion. I still need help please. Kerry "Pete_UK" wrote: Is the reference date in P1 or K8 ? You quote both. You could do this with an INDEX/MATCH combination, but it's getting a bit late here so I'll have to leave that till tomorrow. Let me know if you still need help then. Pete On Dec 3, 11:58 pm, ksean wrote: Phil Smith & Pete_UK, Your answers worked great however now I need to make the situation a whole bunch more complicated. In =IF(E3="U6",120,0) the 120 refers to a value that is date sensitive and in cells M1, N1 or O1. For example: 120 would be the answer from Aug 1, 09 to Aug 25, 09 in cell M1 130 would be the answer from August 26, 09 to Sept 3, 09 in cell N1 150 would be the answer for any date after Sept 3, 09 in cell O1 The reference date would be in cell P1 K L M N O Aug 1 - 25 Aug 26 - Sept 3 Sept 4 + 1 U6 Comm 120 130 150 2 U8 Comm 175 185 205 3 U10-U16 Comm 280 290 330 4 U10-U16 Dev 340 350 395 5 U10-U16 Tier 350 360 405 6 U18 Comm 300 310 350 7 8 8/10/09 If the reference date in cell K8 was August 10, 09 then the value in question would be found in cell M1 however if the reference date was August 28, 09 then the value in question would be found in cell N1 The same thing would also have to happen in the other sections of the formula thus making the formula a whole bunch more complicated. Any thoughts on how I would incorporate this back into the formula. Thanks Kerry "Pete_UK" wrote: Try this: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND (G3="com",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,350,0) + IF(AND (G3="dev",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,380,0) + IF(AND (G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3="U16") ),410,0) Hope this helps. Pete On Dec 3, 9:16 pm, ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or = "U8" then 150 or = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - . |
|
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF OR problem
Well, I'm glad it worked for you - thanks for feeding back.
It would be a lot easier if you were to list the variations as in your reply to Brad (including those for U6, U8 and U18) and then use 3 columns to list the date-related values against each combination. Then you could use a VLOOKUP against this table by concatenating G3 and E3. The formula would be a lot shorter. Hope this helps. Pete On Dec 5, 12:12*am, Ksean wrote: Pete, Sorry for the data mixup, I actually mixed up two little sample tables I was working with. Once I corrected for the < error in the last IF statement, it should have been your index formula works great and yes this is going to turn in to one horrendous formula. As Phil indicated I think I am going to spend a little time rethinking the diretion I am going with this formula to see if I can simplify my approach Thanks, Kerry "Pete_UK" wrote: Sorry, I missed a closing bracket at the end of that INDEX snippet - there should be 3. Pete On Dec 4, 9:06 pm, Pete_UK wrote: This is the formula that I gave you: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND(G3="com",OR(E3="U10",E3="U12",E3="U14",E3=" U16")),350,0) + IF(AND(G3="dev",OR(E3="U10",E3="U12",E3="U14",E3=" U16")),380,0) + IF(AND(G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3= "U16")),410,0) I've manually split it so that it is easier to read. On the first row above, you want the 120 in the first IF to be replaced with a formula that will pick from M1:O1 depending on the date. Similarly, the 150 in the second IF is chosen from M2:O2 depending on the date, although the values you show are 175, 185 and 205. The 375 in the third IF should come from row 6, but there you show the values 300, 310 and 350 in your table. The 4th IF relates to your row 3, where the values are 280, 290, 330, rather than the 350 currently in the formula. The 380 in the 5th IF should come from row 4, where you have the values 340, 350 and 395, and the final IF has a value of 410 in the formula which should be replaced by one of the values from row 5, i.e. 350, 360 or 405. Only your know if these values are correct, and why they differ from what you first posted, but I spell them out here so that you can see clearly what has to be changed. In the first IF you can change the 120 to this: INDEX(M1:O6,1,IF(K8<=--"Aug 25, 09",1,IF(K8<=--"Sept 3, 09",2,3)) This is not fully testing the value in K8, but assumes that it is validated elsewhere. Now, you can replace each of those numbers referred to above with a very similar formula - the only difference is that you replace the 1 after the O6, with the row number that the combination relates to. As Phil states above, this will be a horrendous formula, and I certainly wouldn't like to amend it if things change in the future. Hope this helps. Pete On Dec 4, 5:50 pm, ksean wrote: Pete, I moved it from P1 to K8 for the purpose of problem, sorry for the confusion. I still need help please. Kerry "Pete_UK" wrote: Is the reference date in P1 or K8 ? You quote both. You could do this with an INDEX/MATCH combination, but it's getting a bit late here so I'll have to leave that till tomorrow. Let me know if you still need help then. Pete On Dec 3, 11:58 pm, ksean wrote: Phil Smith & Pete_UK, Your answers worked great however now I need to make the situation a whole bunch more complicated. In *=IF(E3="U6",120,0) *the 120 refers to a value that is date sensitive and in cells M1, N1 or O1. For example: 120 would be the answer from Aug 1, 09 to Aug 25, 09 in cell M1 130 would be the answer from August 26, 09 to Sept 3, 09 in cell N1 150 would be the answer for any date after Sept 3, 09 in cell O1 The reference date would be in cell P1 * * * * K * * * L * * * M * * * N * * * O * * * * * * * * * * * * Aug 1 - 25 * * *Aug 26 - Sept 3 Sept 4 + 1 * * * U6 * * *Comm * *120 * * 130 * * 150 2 * * * U8 * * *Comm * *175 * * 185 * * 205 3 * * * U10-U16 Comm * *280 * * 290 * * 330 4 * * * U10-U16 Dev * * 340 * * 350 * * 395 5 * * * U10-U16 Tier * *350 * * 360 * * 405 6 * * * U18 * * Comm * *300 * * 310 * * 350 7 * * * * * * * * * * * * * * * * * * * * 8 * * * 8/10/09 * * * * * * * * * * * * * If the reference date in cell K8 was August 10, 09 then the value in question would be found in cell M1 however if the reference date was August 28, 09 then the value in question would be found in cell N1 The same thing would also have to happen in the other sections of the formula thus making the formula a whole bunch more complicated. Any thoughts on how I would incorporate this back into the formula. Thanks Kerry "Pete_UK" wrote: Try this: =IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",3 75,0) + IF(AND (G3="com",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,350,0) + IF(AND (G3="dev",OR(E3="U10",E3="U12",E3="U14",E3="U16")) ,380,0) + IF(AND (G3="Tier",OR(E3="U10",E3="U12",E3="U14",E3="U16") ),410,0) Hope this helps. Pete On Dec 3, 9:16 pm, ksean wrote: Not sure where to start with this formula but I need a single formula that results in multiple possible answers. Here is what I have If E3 = "U6" then 120 or * * * * = "U8" then 150 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = com then 350 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = dev then 380 or * * * * = "U10" or "U12" or "U14" or "U16" and G3 = Tier then 410 or * * * * = "U18" then 375 Cell E3 could have 7 possible entries and cell G3 could have 3 possible entries The answer needs to be 120 or 150 or 350 or 380 or 410 or 375 This all needs to be in one formula. .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| big problem | Excel Discussion (Misc queries) | |||
| xla problem | Excel Discussion (Misc queries) | |||
| Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
| #N/A problem | Excel Worksheet Functions | |||
| problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Linear Mode
