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 -