IF OR problem

[ksean]

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.

 

[Phil Smith]

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)),350,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 combinatios
work. You are using AND primarily, and all of your possible ORs are
rapped up together with the and. At least one of the equations in the
Or() must be true, and the standalone gw-"com"ust be true, for that if()
statement to be considered true, and yield 350.

Make sense?

Phil

 

[Phil Smith]

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)),350,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

 

[Paul C]

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.

 

[Brad]

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),VLOOKUP(G3,$B$6:$C$8,2,FALSE))

Copy down as needed.

 

[Pete_UK]

Try this:

=IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",375,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

 

[Chip Pearson]

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]

 

[ksean]

Paul,

You missed part of the scope of the question.

Kerry

 

[ksean]

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

 

[ksean]

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

 

[ksean]

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

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]

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â€

.

 

[Chip Pearson]

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]

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

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]

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”

.

 

[Pete_UK]

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

 

[ksean]

Pete,

I moved it from P1 to K8 for the purpose of problem, sorry for the confusion.

I still need help please.

Kerry

 

[ksean]

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

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]

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

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


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â€
.

.

 

[Phil Smith]

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.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Raze and Rebuild
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_04_09.xlsx

 

[Pete_UK]

This is the formula that I gave you:

=IF(E3="U6",120,0)+IF(E3="U8",150,0)+IF(E3="U18",375,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

 

[Pete_UK]

Sorry, I missed a closing bracket at the end of that INDEX snippet -
there should be 3.

Pete

 

[Brad]

I disagree, The "U" code doesn't matter just the word
Unless you have "words" in U6, u8 or u18